﻿import pymysql
from django.http import HttpResponseRedirect
from django.shortcuts import HttpResponse, render, redirect
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
import json
from django.http import JsonResponse
from qiniu import Auth, put_file, etag
import redis
import jieba
import json
import time
import random
import datetime
from utils.pay import AliPay
from math import sqrt
import operator
from math import *
import math
from aip import AipImageCensor
import re
from myapp import imgTest
from aip import AipSpeech
import wave

r = redis.Redis(host='139.196.136.63', port=6379)
conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)

dict1 = {'xuanhuan': '玄幻', 'qihuan': '奇幻', 'wuxia': '武侠', 'xianxia': '仙侠', 'dushi': '都市', 'xianshi': '现实',
         'junshi': '军事',
         'lishi': '历史', 'youxi': '游戏', 'tiyu': '体育', 'kehuan': '科幻', 'lingyi': '灵异', 'erciyuan': '二次元',
         'duanpian': '短片'}
dict2 = {'lianzai': '连载中', 'wanben': '完本'}
dict3 = {'free': [(-1), 0], 'fufei': [0, 1000]}
dict4 = {'w1': [0, 30 * 10 ** 4], 'w2': [30 * 10 ** 4, 50 * 10 ** 4], 'w3': [50 * 10 ** 4, 100 * 10 ** 4],
         'w4': [100 * 10 ** 4, 200 * 10 ** 4], 'w5': [200 * 10 ** 4, 6000 * 10 ** 4]}

dict5 = {'free': '免费', 'fufei': '付费'}
dict6 = {'w1': '30万字以下', 'w2': '30-50万字', 'w3': '50-100万字', 'w4': '100-200万字', 'w5': '200万字以上'}


def save_wave_file(filename, data):
    framerate = 8000
    NUM_SAMPLES = 2000
    channels = 1
    sampwidth = 1
    TIME = 2
    # '''save the date to the wavfile'''
    wf = wave.open(filename, 'wb')
    wf.setnchannels(channels)
    wf.setsampwidth(sampwidth)
    wf.setframerate(framerate)
    wf.writeframes(b"".join(data))
    wf.close()


def ceshi(request):
    if (request.method == 'GET'):
        return render(request, '录音.html')
    else:
        audio = request.FILES.get('audioData')
        print(type(audio))
        save_wave_file('media/upload/04.pcm', audio)
        APP_ID = '14751903'
        API_KEY = 'sFEqRLr9lp9MmL3jkqzUkVdI'
        SECRET_KEY = 'yz6RR4LbPpiSHboHguObh2VvFOGUsZWm'
        client = AipSpeech(APP_ID, API_KEY, SECRET_KEY)

        # 读取文件
        def get_file_content(filePath):
            with open(filePath, 'rb') as fp:
                return fp.read()

        # 识别本地文件
        result = client.asr(get_file_content('media/upload/04.pcm'), 'pcm', 8000, {'dev_pid': 1536, })
        print(result)
        try:
            if result['result'][0]:
                url = '/query/?keys=' + result['result'][0]
                return HttpResponse(url)
        except:
            return HttpResponse('识别失败')


def Header(request):
    try:
        userid = request.Session.get('user_id')
        conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        sql = "select * from n_user where user_id='%s'" % (userid)
        cursor.execute(sql)
        user = cursor.fetchone()
        print(user)
        request.session['user_name'] = user['user_name']
        return render(request, 'Header.html')
    except:
        i = 1
        return render(request, 'Header.html')


def Details(request):
    hajk='none'
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    user_ip = request.META['REMOTE_ADDR']
    book_id = request.GET.get("book_id")
    book_id = int(book_id)
    print(book_id)
    # 修改
    allow = request.GET.get('allow')
    try:
        user_id = request.session['user_id']
        info = 'in'
    except:
        info = 'out'
    print('===================================')
    print(info)
    nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if info == 'in':
        cursor.execute("select book_id from n_buyrecord where user_id=%s", [user_id])
        books = cursor.fetchall()

        cursor.execute("select * from n_clickrecord where user_id=%s and book_id=%s", [user_id, book_id])
        ad = cursor.fetchone()

        if ad == None:
            cursor.execute("insert into n_clickrecord (user_id,book_id,user_ip,click_time)values(%s,%s,%s,%s)",
                           [user_id, book_id, user_ip, nowTime])
            conn.commit()
            cursor.execute('select book_clickcount from n_bookinfo where book_id=%s', [book_id])
            req = cursor.fetchone()
            print('???????????????????????????????????????')
            print(req['book_clickcount'])
            cursor.execute(" update n_bookinfo set book_clickcount=%s where book_id=%s",
                           [req['book_clickcount'] + 1, book_id])
            conn.commit()

        for row in books:
            print(type(row['book_id']))
            if row['book_id'] == int(book_id):
                info = 'true'

        cursor.execute(
            "select read_id  from n_readrecord where user_id=%s and book_id=%s ",
            [user_id, book_id])
        record = cursor.fetchone()
        if record != None:
            cursor.execute(
                "select max(read_id)  from n_readrecord where user_id=%s and book_id=%s ",
                [user_id, book_id])
            record = cursor.fetchone()
            maxid = record['max(read_id)']
            cursor.execute("select * from n_readrecord where read_id=%s ", [maxid])
            hajk = cursor.fetchone()
            print(hajk)
        print('retrttytytytytytyt')
    else:
        cursor.execute("select count(*) from n_clickrecord where user_ip=%s and user_id=1", [user_ip])
        de = cursor.fetchone()
        if de['count(*)'] <= 100:
            ui = 1
            cursor.execute("insert into n_clickrecord (user_id,book_id,user_ip,click_time )values(%s,%s,%s,%s)",
                           [ui, book_id, user_ip, nowTime])
            conn.commit()
            cursor.execute('select book_clickcount from n_bookinfo where book_id=%s', [book_id])
            req = cursor.fetchone()
            cursor.execute(" update n_bookinfo set book_clickcount=%s where book_id=%s",
                           [req['book_clickcount'] + 1, book_id])
            conn.commit()

    cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
    novels_list = cursor.fetchone()
    print(novels_list)
    cursor.execute("select * from n_user where user_id=%s", [novels_list['user_id'], ])
    user_list = cursor.fetchone()
    print(user_list)
    cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
    kind_list = cursor.fetchone()
    print(kind_list)
    print(user_list['user_id'])
    cursor.execute("select * from n_bookinfo where user_id=%s and book_id!=%s", [user_list['user_id'], book_id, ])

    others_novels = cursor.fetchall()
    if others_novels:
        others_novels1 = others_novels[0]
        print(others_novels)
    else:
        others_novels1 = 1
    print(novels_list['kind_id'])
    cursor.execute("select * from n_bookinfo where kind_id=%s and book_id!=%s limit 4",
                   [novels_list['kind_id'], book_id, ])
    same_novels = cursor.fetchall()
    print(same_novels)
    cursor.execute(
        "select * from n_bookinfo join n_kind on n_bookinfo.kind_id=n_kind.kind_id  where n_bookinfo.kind_id=%s and book_id!=%s limit 8",
        [novels_list['kind_id'], book_id, ])
    same_novels_1 = cursor.fetchall()
    print(same_novels_1)
    cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
    chapter_list = cursor.fetchall()
    print(chapter_list)
    if chapter_list:
        chapter_list1 = chapter_list[0]
        yuedu_button = 0
    else:
        chapter_list1 = 1
        yuedu_button = 1
    # 评论
    cursor.execute(
        "select * from n_talk join n_user on n_talk.user_id=n_user.user_id where n_talk.book_id=%s and parent_id IS NULL limit 5",
        [book_id, ])
    talk_list = cursor.fetchall()
    # 查获价格
    cursor.execute("select book_price from n_bookinfo where book_id=%s ", [book_id])
    rprice = cursor.fetchone()

    cursor.execute(
        "select * from n_bookinfo a,n_user b,n_kind c where a.user_id=b.user_id and a.kind_id=c.kind_id and book_id=%s",
        [book_id])
    bookmag = cursor.fetchone()
    print(talk_list)
    shujia = "加入书架"
    try:
        user_id = request.session['user_id']
        cursor.execute("select * from n_bookcase where book_id=%s and user_id=%s", [book_id, user_id, ])
        n_bookcase_list = cursor.fetchone()
        if n_bookcase_list == None:
            shujia = "加入书架"
        else:
            shujia = "已收藏"
    except:
        print("没登录")
    return render(request, 'Details.html',
                  {'novels_list': novels_list, 'user_list': user_list, 'kind_list': kind_list,
                   'others_novels': others_novels1, 'same_novels': same_novels, 'chapter_list': chapter_list,
                   'talk_list': talk_list, 'same_novels_1': same_novels_1, 'chapter_list1': chapter_list1,
                   'shujia': shujia, 'rprice': rprice, 'bookmag': bookmag, 'info': info, 'allow': allow,
                   'yuedu_button': yuedu_button,'hajk':hajk})


def toupiao(request):
    try:
        conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        user_id = request.session['user_id']
        data = request.POST
        print(user_id)
        print(data)
        cursor.execute("select user_votes from n_user where user_id=%s", [user_id, ])
        user_votes = cursor.fetchone()
        print(user_votes)
        counts = int(user_votes['user_votes'])
        if counts == 0:
            title = "推荐票不足请重新选择！"
            response = JsonResponse({"title": title})
            return response
        votecount = data['votecount'].strip()
        if votecount == "一张月票":
            count = 1
        elif votecount == "二张月票":
            count = 2
        elif votecount == "三张月票":
            count = 3
        elif votecount == "全部月票":
            count = int(user_votes['user_votes'])
        count1 = counts - count
        print(count1)
        if count1 >= 0:
            cursor.execute("update n_user set user_votes=%s where user_id=%s", [count1, user_id, ])
            conn.commit()
        if count1 < 0:
            title = "推荐票不足请重新选择！"
            response = JsonResponse({"title": title})
            return response
        else:
            print(data['book_id'])
            cursor.execute("select book_ticketcount from n_bookinfo where book_id=%s", [data['book_id'], ])
            book_ticketcount = cursor.fetchone()
            book_ticketcount = int(book_ticketcount['book_ticketcount'])
            cursor.execute("update n_bookinfo set book_ticketcount=%s where book_id=%s",
                           [count + book_ticketcount, data['book_id'], ])
            conn.commit()
            cursor.execute("select book_ticketcount from n_bookinfo where book_id=%s", [data['book_id'], ])
            book_ticketcount1 = cursor.fetchone()
            title = "恭喜投票成功！"
            response = JsonResponse({"title": title, "book_ticketcount": book_ticketcount1['book_ticketcount']})
            return response
    except:
        title = "请先登录！！！"
        response = JsonResponse({"title": title})
        return response


def shujia(request):
    try:
        user_id = request.session['user_id']
        print(user_id)
        data = request.POST
        cursor.execute("select * from n_bookcase where book_id=%s and user_id=%s", [data['book_id'], user_id, ])
        n_bookcase_list = cursor.fetchone()
        if n_bookcase_list != None:
            title = "请不要重复收藏！！！"
            response = JsonResponse({"title": title})
            return response
        cursor.execute("select max(bookcase_id) from n_bookcase")
        max_score_id = cursor.fetchone()
        print(max_score_id)
        if max_score_id['max(bookcase_id)'] == None:
            bookcase_id = 0
        else:
            bookcase_id = max_score_id['max(bookcase_id)']
        bookcase_id = bookcase_id + 1
        cursor.execute(
            "insert into n_bookcase(bookcase_id,book_id,user_id) values(%s,%s,%s)",
            [bookcase_id, data['book_id'], user_id, ])
        conn.commit()
        response = JsonResponse({"data": 1})
        return response
    except:
        title = "请先登录！！！"
        response = JsonResponse({"title": title})
        return response


def pingfen(request):
    title = ""
    try:
        user_id = request.session['user_id']
        print(user_id)
        data = request.POST
        index = data['index']
        print(index)
        if index == "":
            title = "请先评价！！！"
            response = JsonResponse({"title": title})
            return response
        cursor.execute("select * from n_scorerecord where book_id=%s and user_id=%s", [data['book_id'], user_id])
        lst = cursor.fetchone()
        if lst:
            title = "请不要重复评价！！！"
            response = JsonResponse({"title": title})
            return response
        title = "评分成功！！！"
        print(title)
        index = int(index) + 1
        score = index * 2
        cursor.execute("select book_estimate,book_estimatecount from n_bookinfo where book_id=%s", [data['book_id'], ])
        book_estimate = cursor.fetchone()
        print(book_estimate['book_estimate'])
        print(book_estimate['book_estimatecount'])
        book_estimate_1 = (book_estimate['book_estimate'] * book_estimate['book_estimatecount'] + score) / (
                book_estimate['book_estimatecount'] + 1)
        book_estimate_1 = '%.1f' % book_estimate_1
        cursor.execute("update n_bookinfo set book_estimate=%s where book_id=%s",
                       [book_estimate_1, data['book_id'], ])
        conn.commit()
        cursor.execute("select book_estimate from n_bookinfo where book_id=%s", [data['book_id'], ])
        book_estimate2 = cursor.fetchone()
        book_estimate3 = '%.1f' % (book_estimate2['book_estimate'])
        cursor.execute("select max(score_id) from n_scorerecord")
        max_score_id = cursor.fetchone()
        print(max_score_id['max(score_id)'] + 1)
        score_id = max_score_id['max(score_id)'] + 1
        cursor.execute(
            "insert into n_scorerecord(score_id,user_id,book_id,book_estimate) values(%s,%s,%s,%s)",
            [score_id, user_id, data['book_id'], score, ])
        conn.commit()
        cursor.execute("select book_estimatecount from n_bookinfo where book_id=%s", [data['book_id'], ])
        book_estimatecount = cursor.fetchone()
        book_estimatecount = int(book_estimatecount['book_estimatecount']) + 1
        cursor.execute("update n_bookinfo set book_estimatecount=%s where book_id=%s",
                       [book_estimatecount, data['book_id'], ])
        conn.commit()
        response = JsonResponse(
            {"title": title, "book_estimatecount": book_estimatecount, "book_estimate": book_estimate3})
        return response
    except:
        title = "请先登录！！！"
        response = JsonResponse({"title": title})
        return response


def Talk(request):
    if request.method == "GET":
        book_id = request.GET.get("book_id")
        cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
        novels_list = cursor.fetchone()
        print(novels_list)
        cursor.execute("select * from n_user where user_id=%s", [novels_list['user_id'], ])
        user_list = cursor.fetchone()
        print(user_list)
        cursor.execute(
            "select * from n_talk join n_user on n_talk.user_id=n_user.user_id where n_talk.book_id=%s and parent_id IS NULL",
            [book_id, ])
        talk_list = cursor.fetchall()
        print(len(talk_list))
        return render(request, 'Talk.html',
                      {'novels_list': novels_list, 'user_list': user_list, 'talk_list': talk_list,
                       'len_talk_list': len(talk_list)})
    else:
        try:
            user_id = request.session['user_id']
            time_now = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            print(time_now)
            data = request.POST
            cursor.execute("select max(talk_id) from n_talk ")
            max_talk_id = cursor.fetchone()
            max_talk_id = max_talk_id['max(talk_id)'] + 1
            cursor.execute(
                "insert into n_talk(talk_id,talk_content,talk_time,talk_like,response_count,book_id,user_id) values(%s,%s,%s,%s,%s,%s,%s)",
                [max_talk_id, data['html'], time_now, 0, 0, data['book_id'], user_id, ])
            conn.commit()
            cursor.execute(
                "select * from n_talk join n_user on n_talk.user_id=n_user.user_id where n_talk.book_id=%s and parent_id IS NULL",
                data['book_id'])
            talk_list = cursor.fetchall()
            print(talk_list)
            len_talk_list = len(talk_list)
            print(len_talk_list)
            response = JsonResponse({"data": talk_list, "len_talk_list": len_talk_list})
            return response
        except:
            title = "请先登录！！！"
            response = JsonResponse({"title": title})
            return response


def TalkDetails(request):
    if request.method == "GET":
        talk_id1 = request.GET.get("talk_id")
        print(talk_id1)
        cursor.execute("select * from n_talk join n_bookinfo on n_talk.book_id=n_bookinfo.book_id where talk_id=%s",
                       [talk_id1, ])
        book_list = cursor.fetchone()
        print(book_list)
        cursor.execute("select * from n_talk join n_user on n_talk.user_id=n_user.user_id where talk_id=%s",
                       [talk_id1, ])
        talk_list_1 = cursor.fetchone()
        print(talk_list_1)
        cursor.execute("select * from n_talk where book_id=%s", [book_list['book_id']])
        talk_list = cursor.fetchall()
        print(talk_list)
        for i in talk_list:
            i['children_contents'] = []
        print(talk_list)
        comment_dict = {}
        for d in talk_list:
            talk_id = d.get('talk_id')
            comment_dict[talk_id] = d
        print(comment_dict)
        for k in comment_dict:
            parent_id = comment_dict[k]['parent_id']
            if parent_id:
                comment_dict[parent_id]['children_contents'].append(comment_dict[k])
        print(comment_dict)
        res_list = []
        for i in comment_dict:
            if not comment_dict[i]['parent_id']:
                res_list.append(comment_dict[i])
        print(res_list[0])
        print('+++++++++++++++++++++++')
        print(talk_id1)
        lst1 = []
        for j in res_list:
            print(j)
            if int(j['talk_id']) == int(talk_id1):
                print(1)
                lst1.append(j)
        print(lst1)
        print('+++++++++++++++++++++++')

        print(lst1)
        global lst
        lst = []
        response = get_content(lst1)
        response1 = sorted(response, key=lambda x: int(x['talk_id']))
        print(response1)
        k = 1
        for i in response1:
            i['floor_id'] = k
            k += 1
        return render(request, 'TalkDetails.html',
                      {'talk_list_1': talk_list_1, 'response': response1, 'book_list': book_list})
    else:
        try:
            user_id = request.session['user_id']
            time_now = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            print(time_now)
            data = request.POST
            print(data['talk_id'])
            cursor.execute("select max(talk_id) from n_talk ")
            max_talk_id = cursor.fetchone()
            max_talk_id = max_talk_id['max(talk_id)'] + 1
            print(max_talk_id)
            cursor.execute(
                "insert into n_talk(talk_id,talk_content,talk_time,talk_like,response_count,book_id,user_id,parent_id) values(%s,%s,%s,%s,%s,%s,%s,%s)",
                [max_talk_id, data['html'], time_now, 0, 0, data['book_id'], user_id, data['talk_id'], ])
            conn.commit()
            cursor.execute("select * from n_talk where talk_id=%s", [max_talk_id, ])
            user_talk = cursor.fetchone()
            print(add_response(user_talk))
            print(user_talk)
            print(data['talk_id'])
            print(data['book_id'])
            print(data['html'])
            cursor.execute("select * from n_talk where book_id=%s", [data['book_id']])
            talk_list = cursor.fetchall()
            print(talk_list)
            for i in talk_list:
                i['children_contents'] = []
            print(talk_list)
            comment_dict = {}
            for d in talk_list:
                talk_id = d.get('talk_id')
                comment_dict[talk_id] = d
            print(comment_dict)
            for k in comment_dict:
                parent_id = comment_dict[k]['parent_id']
                if parent_id:
                    comment_dict[parent_id]['children_contents'].append(comment_dict[k])
            print(comment_dict)
            res_list = []
            for i in comment_dict:
                if not comment_dict[i]['parent_id']:
                    res_list.append(comment_dict[i])
            print("+++++++++++++++++++++")
            print(data['talk_id'])
            cursor.execute("select * from n_talk where talk_id=%s", [data['talk_id'], ])
            talk_chid = cursor.fetchone()
            print("+++++++++++++++++")
            abc = find(talk_chid)
            # print(talk_id)
            lst1 = []
            for j in res_list:
                print(j)
                if int(j['talk_id']) == abc:
                    lst1.append(j)
            print(lst1)
            print("+++++++++++++++")
            lst = []
            response = get_content(lst1)
            response1 = sorted(response, key=lambda x: int(x['talk_id']))
            print(response1)
            k = 1
            for i in response1:
                i['floor_id'] = str(k)
                k += 1
            response = JsonResponse({"data": response1})
            return response
        except:
            title = "请先登录！！！"
            response = JsonResponse({"title": title})
            return response


def find(talk_chid):
    abc = talk_chid['talk_id']
    if talk_chid['parent_id']:
        cursor.execute("select * from n_talk where talk_id=%s", [talk_chid['parent_id'], ])
        talk_parent = cursor.fetchone()
        abc = find(talk_parent)
        return abc
    else:
        return abc


def add_response(user_talk):
    if user_talk['parent_id']:
        cursor.execute("select * from n_talk where talk_id=%s", [user_talk['parent_id'], ])
        user_parent = cursor.fetchone()
        cursor.execute("update n_talk set response_count=%s where talk_id=%s",
                       [user_parent['response_count'] + 1, user_parent['talk_id']])
        conn.commit()
        if user_parent['parent_id']:
            add_response(user_parent)
    return 1


def get_content(lst1):
    for i in lst1:
        if i['parent_id']:
            dict = {}
            cursor.execute("select * from n_talk where talk_id=%s", [i['parent_id'], ])
            user_parent = cursor.fetchone()
            cursor.execute("select * from n_user where user_id=%s", [user_parent['user_id'], ])
            user_parent1 = cursor.fetchone()
            cursor.execute("select * from n_user where user_id=%s", [i['user_id'], ])
            user_child = cursor.fetchone()
            dict['talk_id'] = str(i['talk_id'])
            dict['user_id'] = str(user_child['user_id'])
            dict['user_name'] = user_child['user_name']
            dict['parent_name'] = user_parent1['user_name']
            dict['talk_content'] = i['talk_content']
            dict['response_count'] = str(i['response_count'])
            dict['talk_like'] = str(i['talk_like'])
            dict['talk_time'] = str(i['talk_time'])
            dict['user_image'] = user_child['user_image']
            lst.append(dict)
        if i['children_contents']:
            get_content(i['children_contents'])
    return lst


def Likes1(request):
    try:
        user_id = request.session['user_id']
        data = request.POST
        print(type(data['talk_id']))
        cursor.execute("select * from n_talk where talk_id=%s", [data['talk_id'], ])
        talk_1 = cursor.fetchone()
        print(talk_1['talk_like'])
        talk_like = talk_1['talk_like'] + 1
        cursor.execute("update n_talk set talk_like=%s where talk_id=%s",
                       [talk_like, data['talk_id'], ])
        conn.commit()
        response = JsonResponse({"talk_like": talk_like, 'response_count': talk_1['response_count']})
        return response
    except:
        title = "请先登录！！！"
        response = JsonResponse({"title": title})
        return response


def Likes2(request):
    try:
        user_id = request.session['user_id']
        data = request.POST
        print(type(data['talk_id']))
        cursor.execute("select * from n_talk where talk_id=%s", [data['talk_id'], ])
        talk_1 = cursor.fetchone()
        print(talk_1['talk_like'])
        talk_like = talk_1['talk_like'] - 1
        cursor.execute("update n_talk set talk_like=%s where talk_id=%s",
                       [talk_like, data['talk_id'], ])
        conn.commit()
        response = JsonResponse({"talk_like": talk_like, 'response_count': talk_1['response_count']})
        return response
    except:
        title = "请先登录！！！"
        response = JsonResponse({"title": title})
        return response


def MyTalk(request):
    user_id = request.GET.get("user_id")
    cursor.execute(
        "select * from n_talk join n_user on n_talk.user_id=n_user.user_id where n_talk.user_id=%s and parent_id IS NULL",
        [user_id, ])
    talk_list = cursor.fetchall()
    cursor.execute(
        "select * from n_talk join n_user on n_talk.user_id=n_user.user_id where parent_id=%s",
        [user_id, ])
    talk_list1 = cursor.fetchall()
    return render(request, 'MyTalk.html', {'talk_list': talk_list, 'talk_list1': talk_list1})


def Read(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    nowTime_1 = (datetime.datetime.now() - datetime.timedelta(minutes=2)).strftime("%Y-%m-%d %H:%M:%S")
    user_ip = request.META['REMOTE_ADDR']
    cursor.execute("select * from n_clickrecord where click_time>%s and user_ip=%s", [nowTime_1, user_ip, ])
    Time_list = cursor.fetchall()
    print(len(Time_list))
    if len(Time_list) > 50:
        return HttpResponse(status=403)
    print(user_ip)
    header = request.META['HTTP_USER_AGENT']
    print(header)
    chapter_id = request.GET.get("chapter_id")
    book_id = request.GET.get("book_id")
    cursor.execute("insert into n_clickrecord (user_id,book_id,user_ip,click_time)values(%s,%s,%s,%s)",
                   [1, book_id, user_ip, nowTime])
    conn.commit()
    # xiugai
    info = ''
    try:
        user_id = request.session['user_id']
        info = 'in'
    except:
        info = 'out'

    print(info)
    if info == 'in':
        cursor.execute("insert into n_readrecord (book_id,chapter_id,read_time,user_id)values(%s,%s,%s,%s)",
                       [book_id, chapter_id, nowTime, user_id])
        conn.commit()

    print(chapter_id)
    print(book_id)
    if chapter_id == None:
        chapter_id = 1
    cursor.execute("select * from n_chapter where chapter_id=%s and book_id=%s", [chapter_id, book_id])
    chapter = cursor.fetchone()
    print(chapter)
    cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
    chapter_list = cursor.fetchall()
    print(chapter_list)
    if chapter_list:
        chapter_list1 = chapter_list[0]
    else:
        chapter_list1 = 1
    return render(request, 'Read.html',
                  {'chapter': chapter, 'chapter_list': chapter_list, 'chapter_list1': chapter_list1,
                   'book_id': book_id}, )


def Write_book(request):
    if request.method == 'GET':
        try:
            user_id = request.session['user_id']
            return render(request, 'Write_book.html')
        except:
            return redirect('/login/')
    else:
        try:
            user_id = request.session['user_id']
            name = request.POST.get('name')
            kind = request.POST.get('kind')
            text = request.POST.get('content')
            print(name, kind, text)
            File = request.FILES.get("img")
            print(File)
            try:
                with open('media/upload/img/' + File.name, 'wb')as f:  # with open 无法创建文件夹，需要自己创建
                    for chunk in File.chunks():
                        f.write(chunk)
                access_key = 'kWMzCb6yGVJllsSSiYShDXZ3AIRyJ3T0FLAB96hF'
                secret_key = 'EOYODZJ8zPfG5UfRU3ddlA1h9iU9N5lJAqKQaFoi'
                q = Auth(access_key, secret_key)
                bucket_name = 'kuyue'
                key = File.name
                localfile = r"media/upload/img/" + File.name
                try:
                    res = imgTest.run(localfile)
                    print('<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<>>>>>>>')
                    print(res['conclusion'], res['data'][0]['msg'])
                    if res['conclusion'] == '不合规':
                        return render(request, 'Write_book.html',
                                      {'bag': '小说封面' + res['conclusion'] + ',' + res['data'][0]['msg'] + ',上传失败！','name':name,'text':text})
                except:
                    pass
                token = q.upload_token(bucket_name, key)
                ret, info = put_file(token, key, localfile)
                src = 'http://phd9lbnhk.bkt.clouddn.com/' + File.name
                cursor.execute("select max(book_id) from n_examine_book")
                max_book_id = cursor.fetchone()
                book_id = int(max_book_id['max(book_id)']) + 1
                sql = "INSERT INTO n_examine_book(book_id,book_name,book_words,book_introduction,book_image,book_state,book_ticketcount,book_sellcount,book_price,user_id,book_clickcount,book_estimatecount,book_estimate,kind_id)VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    book_id, name, 0, text, src, '连载中', 0, 0, 0, user_id, 0, 0, 0, kind)
                cursor.execute(sql)
                conn.commit()
                ##
                id = request.session['user_id']
                print('OK')
                print(id)
                cursor.execute(
                    "select * from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                        id,))
                authorlist = cursor.fetchall()
                cursor.execute(
                    "select sum(book_words) from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                        id,))
                result = cursor.fetchone()
                words = result['sum(book_words)']
                if words == None:
                    words = 0
                # print(authorlist)
                cursor.execute("select count(*) from n_fans  where user_id='%s'" % (id,))
                fan = cursor.fetchone()
                fan = fan['count(*)']
                # print(len(fan))
                cursor.execute("select count(*) from n_follow  where user_id='%s'" % (id,))
                follow = cursor.fetchone()
                # print(len(follow))
                follow = follow['count(*)']
                num = len(authorlist)
                if authorlist != ():
                    author = authorlist[0]
                else:
                    cursor.execute("select * from n_user where user_id=%s", [id])
                    author = cursor.fetchone()
                lan = len(authorlist)
                print('++++++++++++++++++++++++++')
                print(author)
                return render(request, 'author.html',
                              {'authorlist': authorlist, 'author': author, 'num': num, 'fan': fan, 'follow': follow,
                               'words': words, 'lan': lan})
            except:
                src = '../static/image/59886s.jpg'
                cursor.execute("select max(book_id) from n_examine_book")
                max_book_id = cursor.fetchone()
                book_id = int(max_book_id['max(book_id)']) + 1
                sql = "INSERT INTO n_examine_book(book_id,book_name,book_words,book_introduction,book_image,book_state,book_ticketcount,book_sellcount,book_price,user_id,book_clickcount,book_estimatecount,book_estimate,kind_id)VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    book_id, name, 0, text, src, '连载中', 0, 0, 0, user_id, 0, 0, 0, kind)
                cursor.execute(sql)
                conn.commit()
                ##
                id = request.session['user_id']
                print('OK')
                print(id)
                cursor.execute(
                    "select * from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                        id,))
                authorlist = cursor.fetchall()
                cursor.execute(
                    "select sum(book_words) from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                        id,))
                result = cursor.fetchone()
                words = result['sum(book_words)']
                if words == None:
                    words = 0
                # print(authorlist)
                cursor.execute("select count(*) from n_fans  where user_id='%s'" % (id,))
                fan = cursor.fetchone()
                fan = fan['count(*)']
                # print(len(fan))
                cursor.execute("select count(*) from n_follow  where user_id='%s'" % (id,))
                follow = cursor.fetchone()
                # print(len(follow))
                follow = follow['count(*)']
                num = len(authorlist)
                if authorlist != ():
                    author = authorlist[0]
                else:
                    cursor.execute("select * from n_user where user_id=%s", [id])
                    author = cursor.fetchone()
                lan = len(authorlist)
                print('++++++++++++++++++++++++++')
                print(author)
                return render(request, 'author.html',
                              {'authorlist': authorlist, 'author': author, 'num': num, 'fan': fan, 'follow': follow,
                               'words': words, 'lan': lan})
        except:
            id = request.session['user_id']
            print('OK')
            print(id)
            cursor.execute(
                "select * from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                    id,))
            authorlist = cursor.fetchall()
            cursor.execute(
                "select sum(book_words) from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                    id,))
            result = cursor.fetchone()
            words = result['sum(book_words)']
            if words == None:
                words = 0
            # print(authorlist)
            cursor.execute("select count(*) from n_fans  where user_id='%s'" % (id,))
            fan = cursor.fetchone()
            fan = fan['count(*)']
            # print(len(fan))
            cursor.execute("select count(*) from n_follow  where user_id='%s'" % (id,))
            follow = cursor.fetchone()
            # print(len(follow))
            follow = follow['count(*)']
            num = len(authorlist)
            if authorlist != ():
                author = authorlist[0]
            else:
                cursor.execute("select * from n_user where user_id=%s", [id])
                author = cursor.fetchone()
            lan = len(authorlist)
            print('++++++++++++++++++++++++++')
            print(author)
            return render(request, 'author.html',
                          {'authorlist': authorlist, 'author': author, 'num': num, 'fan': fan, 'follow': follow,
                           'words': words, 'lan': lan})


def My_book(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    book_id = request.GET.get("book_id")
    cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
    novels_list = cursor.fetchone()
    cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
    kind_list = cursor.fetchone()
    cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
    chapter_list = cursor.fetchall()
    return render(request, 'My_book.html',
                  {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list})


def Write_chapter(request):
    if request.method == 'GET':
        book_id = request.GET.get("book_id")
        try:
            user_id = request.session['user_id']
            return render(request, 'Write_chapter.html', {'book_id': book_id})
        except:
            return redirect('/login/')
    else:
        try:
            book_id = request.GET.get("book_id")
            MG_list = []
            name = request.POST.get('name')
            text = request.POST.get('content')
            cursor.execute("select max(chapter_id) from n_chapter")
            max_chapter_id = cursor.fetchone()
            chapter_id = int(max_chapter_id['max(chapter_id)']) + 1
            dr = re.compile(r'<[^>]+>', re.S)
            dd = dr.sub('', text)
            # print(text)
            """ 你的 APPID AK SK """
            # print(text)
            # with open(r'D:\PYCHARM\ai\作业\Kuyue\myapp\wenben', 'r',encoding='utf-8') as f:
            #     str = f.read()
            # print(str)
            with open('/wenben', 'w', encoding='utf-8') as f:
                f.write(dd)
            # print(1111)
            APP_ID = '14709495'
            API_KEY = 'ZQl0OlmcMqjAzYMEBqpFY9Pi'
            SECRET_KEY = 'U0uH7Ibre0TFvGGfNmyo7G4u96QstWXI'
            client = AipImageCensor(APP_ID, API_KEY, SECRET_KEY)
            with open('/wenben', 'r', encoding='utf-8') as f:
                str = f.read()
            # print(11111111111)
            # print(len(str))
            result = client.antiSpam(str)
            print(result)
            for i in result['result']['review']:
                if i['hit'] != []:
                    MG_list.append(i['hit'][0])
                print(i['hit'])
                # print(MG_list)

            print(result['result']['spam'])
            if result['result']['spam'] == 0:
                sql = "INSERT INTO n_chapter(chapter_id,chapter_name,chapter_content,book_id)VALUES('%d','%s','%s','%d')" % (
                    int(chapter_id), name, text, int(book_id))
                cursor.execute(sql)
                conn.commit()
                result = '上传成功'
            elif result['result']['spam'] == 1:
                if MG_list == []:
                    result = '审核不通过'
                else:
                    for i in MG_list:
                        str = ''
                        str += i
                        result = '审核不通过,含有铭感词' + str
            elif result['result']['spam'] == 2:
                sql = "INSERT INTO n_examine(chapter_id,chapter_name,chapter_content,book_id)VALUES('%d','%s','%s','%d')" % (
                    int(chapter_id), name, text, int(book_id))
                cursor.execute(sql)
                conn.commit()
                result = '请等待1-2个审核日'
            print(MG_list)
            cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
            novels_list = cursor.fetchone()
            cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
            kind_list = cursor.fetchone()
            cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
            chapter_list = cursor.fetchall()
            return render(request, 'My_book.html',
                          {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list,
                           'result': result})
        except:
            result = ''
            book_id = request.GET.get("book_id")
            cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
            novels_list = cursor.fetchone()
            cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
            kind_list = cursor.fetchone()
            cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
            chapter_list = cursor.fetchall()
            return render(request, 'My_book.html',
                          {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list,
                           'result': result})


def Update_chapter(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    if request.method == 'GET':
        try:
            user_id = request.session['user_id']
        except:
            return redirect('/login/')
        chapter_id = request.GET.get("chapter_id")
        cursor.execute("select * from n_chapter where chapter_id=%s", [chapter_id, ])
        chapter_list = cursor.fetchone()
        print(chapter_list)
        book_id = request.GET.get("book_id")
        return render(request, 'Update_chapter.html', {'chapter_list': chapter_list, 'book_id': book_id})
    else:
        try:
            book_id = request.GET.get("book_id")
            MG_list = []
            name = request.POST.get('name')
            text = request.POST.get('content')
            cursor.execute("select max(chapter_id) from n_chapter")
            max_chapter_id = cursor.fetchone()
            chapter_id = int(max_chapter_id['max(chapter_id)'])
            print(chapter_id)
            print(name)
            print(text)
            print(book_id)
            dr = re.compile(r'<[^>]+>', re.S)
            dd = dr.sub('', text)
            print(dd)
            # print(text)
            """ 你的 APPID AK SK """
            # print(text)
            # with open(r'D:\PYCHARM\ai\作业\Kuyue\myapp\wenben', 'r',encoding='utf-8') as f:
            #     str = f.read()
            # print(str)
            with open('/wenben', 'w', encoding='utf-8') as f:
                f.write(dd)
            # print(1111)
            APP_ID = '14709495'
            API_KEY = 'ZQl0OlmcMqjAzYMEBqpFY9Pi'
            SECRET_KEY = 'U0uH7Ibre0TFvGGfNmyo7G4u96QstWXI'
            client = AipImageCensor(APP_ID, API_KEY, SECRET_KEY)
            with open('/wenben', 'r', encoding='utf-8') as f:
                str = f.read()
            # print(11111111111)
            # print(len(str))
            result = client.antiSpam(str)
            print(result)
            for i in result['result']['review']:
                if i['hit'] != []:
                    MG_list.append(i['hit'][0])
                print(i['hit'])
                # print(MG_list)
            print(result['result']['spam'])
            if result['result']['spam'] == 0:
                sql = "update n_chapter set chapter_name='%s',chapter_content='%s' where chapter_id='%s'" % (
                    name, text, int(chapter_id))
                cursor.execute(sql)
                conn.commit()
                result = '上传成功'
            elif result['result']['spam'] == 1:
                if MG_list == []:
                    result = '审核不通过'
                else:
                    for i in MG_list:
                        str = ''
                        str += i
                        result = '审核不通过,含有铭感词' + str
            elif result['result']['spam'] == 2:
                sql = "update n_examine set chapter_content='%s',chapter_name='%s' where chapter_id='%d' and book_id='%d'" % (
                    text, name, int(chapter_id), int(book_id))
                cursor.execute(sql)
                conn.commit()
                result = '请等待1-2个审核日'
            print(MG_list)
            cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
            novels_list = cursor.fetchone()
            cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
            kind_list = cursor.fetchone()
            cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
            chapter_list = cursor.fetchall()
            return render(request, 'My_book.html',
                          {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list,
                           'result': result})
        except:
            book_id = request.GET.get("book_id")
            cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
            novels_list = cursor.fetchone()
            cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
            kind_list = cursor.fetchone()
            cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
            chapter_list = cursor.fetchall()
            return render(request, 'My_book.html',
                          {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list})


def Update_book(request):
    if request.method == 'GET':
        try:
            user_id = request.session['user_id']
        except:
            return redirect('/login/')
        book_id = request.GET.get("book_id")
        cursor.execute("select book_name,book_introduction,book_image from n_bookinfo where book_id=%s", [book_id, ])
        novels_list = cursor.fetchone()
        return render(request, 'Update_book.html', {'novels_list': novels_list, 'book_id': book_id})
    else:
        try:
            book_id = request.GET.get("book_id")
            name = request.POST.get('name')
            kind = request.POST.get('kind')
            text = request.POST.get('content')
            print(name, kind, text)
            File = request.FILES.get("img")
            print(File)
            try:
                with open('media/upload/img/' + File.name, 'wb')as f:  # with open 无法创建文件夹，需要自己创建
                    for chunk in File.chunks():
                        f.write(chunk)
                access_key = 'kWMzCb6yGVJllsSSiYShDXZ3AIRyJ3T0FLAB96hF'
                secret_key = 'EOYODZJ8zPfG5UfRU3ddlA1h9iU9N5lJAqKQaFoi'
                q = Auth(access_key, secret_key)
                bucket_name = 'kuyue'
                key = File.name
                localfile = r"media/upload/img/" + File.name
                token = q.upload_token(bucket_name, key)
                ret, info = put_file(token, key, localfile)
                src = 'http://phd9lbnhk.bkt.clouddn.com/' + File.name
                cursor.execute(
                    "update n_bookinfo set book_name=%s,book_introduction=%s,book_image=%s,kind_id=%s where book_id=%s",
                    [name, text, src, kind, book_id])
                conn.commit()
                ##
                cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
                novels_list = cursor.fetchone()
                cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
                kind_list = cursor.fetchone()
                cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
                chapter_list = cursor.fetchall()
                return render(request, 'My_book.html',
                              {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list})
            except:
                src = '../static/image/59886s.jpg'
                cursor.execute(
                    "update n_bookinfo set book_name=%s,book_introduction=%s,book_image=%s,kind_id=%s where book_id=%s",
                    [name, text, src, kind, book_id])
                conn.commit()
                ##
                cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
                novels_list = cursor.fetchone()
                cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
                kind_list = cursor.fetchone()
                cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
                chapter_list = cursor.fetchall()
                return render(request, 'My_book.html',
                              {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list})
        except:
            book_id = request.GET.get("book_id")
            cursor.execute("select * from n_bookinfo where book_id=%s", [book_id, ])
            novels_list = cursor.fetchone()
            cursor.execute("select * from n_kind where kind_id=%s", [novels_list['kind_id'], ])
            kind_list = cursor.fetchone()
            cursor.execute("select chapter_id,chapter_name,book_id from n_chapter where book_id=%s", [book_id, ])
            chapter_list = cursor.fetchall()
            return render(request, 'My_book.html',
                          {'novels_list': novels_list, 'kind_list': kind_list, 'chapter_list': chapter_list})


lst5 = []
cursor.execute("select * from n_readrecord group by user_id")
user_id_1 = cursor.fetchall()
for j in user_id_1:
    lst5.append(j['user_id'])
print(lst5)
dic2 = {}
for i in lst5:
    cursor.execute("select * from n_readrecord where user_id=%s group by book_id", [i, ])
    lst1 = cursor.fetchall()
    dic1 = {}
    for j in lst1:
        cursor.execute("select count(*) from n_readrecord where book_id=%s and user_id=%s", [j['book_id'], i, ])
        lst2 = cursor.fetchone()
        number = ((1 / (1 + math.exp((-lst2['count(*)']) / 50))) - 0.5)
        dic1[j['book_id']] = float("%.1f" % (number * 10))
    dic2[str(i)] = dic1
print(dic2)


class recommendation:
    def __init__(self):
        self.critics = dic2

    def sim_distance(self, person1, person2, new_critics=0):
        if new_critics != 0:
            new_critics = self.transformPrefs()
            movielist1 = new_critics[person1]
            movielist2 = new_critics[person2]
            commonlist = []
            for item in movielist1:
                if item in movielist2:
                    commonlist.append(item)
            if len(commonlist) == 0:
                return 0
            distance = sum([pow(movielist1[item] - movielist2[item], 2) for item in commonlist])
            sim = 1 / (sqrt(distance) + 1)
            return sim

    def sim_pearson(self, person1, person2):
        movielist1 = self.critics[person1]
        movielist2 = self.critics[person2]
        commonlist = []
        for item in movielist1:
            if item in movielist2:
                commonlist.append(item)
        if len(commonlist) == 0:
            return 0

        sum1 = sum([self.critics[person1][item] for item in commonlist])
        sum2 = sum([self.critics[person2][item] for item in commonlist])
        sum1sq = sum([pow(self.critics[person1][item], 2) for item in commonlist])
        sum2sq = sum([pow(self.critics[person2][item], 2) for item in commonlist])
        psum = sum([self.critics[person1][item] * self.critics[person2][item]
                    for item in commonlist])
        n = len(commonlist)
        num = (psum - (sum1 * sum2 / n))
        den = sqrt((sum1sq - pow(sum1, 2) / n) * (sum2sq - pow(sum2, 2) / n))
        if den == 0:
            return 0
        r = num / den
        return r

    def topMatches(self, person, n=5, similarity=sim_pearson):
        if person not in self.critics:
            print("person input error!")
            return 0
        scores = [(similarity(self, person, item), item) for item in self.critics if item != person]
        return sorted(scores, key=lambda it: -it[0])[0:n]

    def getRecommendations(self, person, similarity=sim_pearson):
        moviescore_dict = {}
        moviesim_dict = {}
        for other in self.critics:
            sim = similarity(self, other, person)
            if other == person:
                continue
            if sim <= 0:
                continue
            for item in self.critics[other]:
                if item not in self.critics[person]:
                    moviescore_dict.setdefault(item, 0)
                    moviescore_dict[item] += self.critics[other][item] * sim
                    moviesim_dict.setdefault(item, 0)
                    moviesim_dict[item] += sim
        result = [(float(moviescore_dict[item] / moviesim_dict[item]), item)
                  for item in moviescore_dict]
        result = sorted(result, key=lambda e1: -e1[0])
        return result

    def transformPrefs(self):
        result = {}
        for person in self.critics:
            for item in self.critics[person]:
                result.setdefault(item, {})
                result[item][person] = self.critics[person][item]
        return result

    def calculateSimilarItems(self, similarity=sim_distance):  # 物品相似度字典
        result = {}
        reverse_critics = self.transformPrefs()
        for movie in reverse_critics:
            scores = [(similarity(self, movie, item, 100), item)
                      for item in reverse_critics if item != movie and similarity(self, movie, item, 100) != 0]
            scores = sorted(scores, key=lambda it: -it[0])
            result[movie] = scores
        return result

    def getRecommendedItems(self, user):
        userRatings = self.critics[user]
        itemMatch = self.calculateSimilarItems()
        scores = {}
        totalsim = {}
        for item, rating in userRatings.items():
            for similarity, item2 in itemMatch[item]:
                if item2 in userRatings:
                    continue
                scores.setdefault(item2, 0)
                scores[item2] += similarity * rating
                totalsim.setdefault(item2, 0)
                totalsim[item2] += similarity
        ranking = [(score / totalsim[item], item) for item, score in scores.items()]
        ranking = sorted(ranking, key=lambda it: -it[0])
        return ranking


user_goods_lst = {}
for i in range(1, 9):
    system = recommendation()
    user_lst = system.getRecommendations(str(i))[:]
    goods_lst = system.getRecommendedItems(str(i))[:]
    lst2 = user_lst + goods_lst
    user_goods_lst[str(i)] = lst2
print(user_goods_lst)


# 马荣
def main(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'select book_id,book_name,book_ticketcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id   order by book_ticketcount desc'
    cursor.execute(sql)
    tc = cursor.fetchall()
    tc_1 = tc[0]
    tc = tc[1:10]
    # print(tc)
    sql = 'select book_id,book_name,book_sellcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_sellcount desc'
    cursor.execute(sql)
    sc = cursor.fetchall()
    sc_1 = sc[0]
    sc = sc[1:10]
    sql = 'select book_id,book_name,book_words,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_words desc'
    cursor.execute(sql)
    bw = cursor.fetchall()
    bw_1 = bw[0]
    bw = bw[1:10]
    sql = 'select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_clickcount desc'
    cursor.execute(sql)
    cc = cursor.fetchall()
    cc_1 = cc[0]
    cc = cc[1:10]
    try:
        user_id = request.session['user_id']
        lst2 = user_goods_lst[str(user_id)]
        print('_____________________')
        # cursor.execute("select * from n_bookcase")
        # book_list = cursor.fetchall()
        # print(book_list)
        # lst = []
        # for i in book_list:
        #     str1 = ''
        #     str1 += str(i['user_id']) + ',' + str(1) + ',' + str(i['book_id'])
        #     lst.append(str1)
        # print(lst)
        # data = loadData(lst)  # 获得数据
        # W = similarity(data)  # 计算物品相似矩阵
        # lst2 = recommandList(data, W, str(user_id), 5, 10)  # 推荐

        be = []
        for i in lst2:
            cursor.execute(
                "select book_id,book_name,book_estimatecount,book_introduction,user_name from (select book_id,book_name,book_estimatecount,book_introduction,user_name from n_bookinfo,n_user  where n_bookinfo.user_id = n_user.user_id) t where book_id=%s",
                [i[1], ])
            book_list_1 = cursor.fetchone()
            be.append(book_list_1)
        print(be)
        if be == []:
            sql = 'select book_id,book_name,book_estimatecount,book_introduction,user_name from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_estimate desc'
            cursor.execute(sql)
            be = cursor.fetchall()
            be = be[0:10]
        if len(be) < 10:
            sql = 'select book_id,book_name,book_estimatecount,book_introduction,user_name from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_estimate desc'
            cursor.execute(sql)
            be1 = cursor.fetchall()
            be1 = be1[0:10 - len(be)]
            be = be + be1
        print(be)
        be = be[:10]
        ec1 = be[0:3]
        ec2 = be[3:6]
        ec3 = be[6:9]
        print(ec1)
        i = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10']
        for i in be:
            # i['book_introduction']=i['book_introduction'][0:30]

            i['book_introduction'] = i['book_introduction'].strip()[0:30].replace(' ', '').replace('\n', '').replace(
                '\t',
                '').replace(
                '\r', '')
        return render(request, 'main.html',
                      {'tc_1': tc_1, 'tc': tc, 'sc_1': sc_1, 'sc': sc, 'bw_1': bw_1, 'bw': bw, 'cc_1': cc_1, 'cc': cc,
                       'be': be, 'ec1': ec1, 'i': i, 'ec2': ec2, 'ec3': ec3})
    except:
        sql = 'select book_id,book_name,user_name from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_estimate desc'
        cursor.execute(sql)
        be = cursor.fetchall()
        be = be[0:10]
        sql = 'select book_id,book_name,book_estimatecount,book_introduction from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_estimatecount desc'
        cursor.execute(sql)
        ec = cursor.fetchall()
        ec1 = ec[0:3]
        ec2 = ec[3:6]
        ec3 = ec[6:9]
        i = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10']
        for i in ec:
            # i['book_introduction']=i['book_introduction'][0:30]

            i['book_introduction'] = i['book_introduction'].strip()[0:30].replace(' ', '').replace('\n', '').replace(
                '\t',
                '').replace(
                '\r', '')
        return render(request, 'main.html',
                      {'tc_1': tc_1, 'tc': tc, 'sc_1': sc_1, 'sc': sc, 'bw_1': bw_1, 'bw': bw, 'cc_1': cc_1, 'cc': cc,
                       'be': be, 'ec1': ec1, 'i': i, 'ec2': ec2, 'ec3': ec3})


# def loadData(files):
#     data = {}
#     for line in files:
#         user, score, item = line.split(",")
#         data.setdefault(user, {})
#         data[user][item] = score
#     return data
#
#
# def similarity(data):
#     # 构造物品：物品的共现矩阵
#     N = {}
#     C = {}
#     for user, item in data.items():
#         for i, score in item.items():
#             N.setdefault(i, 0)
#             N[i] += 1
#             C.setdefault(i, {})
#             for j, scores in item.items():
#                 if j not in i:
#                     C[i].setdefault(j, 0)
#                     C[i][j] += 1
#
#     print("---2.构造的共现矩阵---")
#     W = {}
#     for i, item in C.items():
#         W.setdefault(i, {})
#         for j, item2 in item.items():
#             W[i].setdefault(j, 0)
#             W[i][j] = C[i][j] / sqrt(N[i] * N[j])
#     print("---3.构造的相似矩阵---")
#     return W
#
#
# def recommandList(data, W, user, k=5, N=10):
#     rank = {}
#     for i, score in data[user].items():
#         for j, w in sorted(W[i].items(), key=operator.itemgetter(1), reverse=True)[0:k]:
#             if j not in data[user].keys():
#                 rank.setdefault(j, 0)
#                 rank[j] += float(score) * w
#     print("---4.推荐----")
#     print(sorted(rank.items(), key=operator.itemgetter(1), reverse=True)[0:N])
#     return sorted(rank.items(), key=operator.itemgetter(1), reverse=True)[0:N]


def main2(request):
    i = 1
    return render(request, 'main.html', {'i': 1})


def ranking(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'select book_id,book_name,book_ticketcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id   order by book_ticketcount desc'
    cursor.execute(sql)
    tc = cursor.fetchall()
    tc_1 = tc[0]
    tc = tc[1:10]
    # print(tc)
    sql = 'select book_id,book_name,book_sellcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_sellcount desc'
    cursor.execute(sql)
    sc = cursor.fetchall()
    sc_1 = sc[0]
    sc = sc[1:10]
    sql = 'select book_id,book_name,book_words,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_words desc'
    cursor.execute(sql)
    bw = cursor.fetchall()
    bw_1 = bw[0]
    bw = bw[1:10]
    sql = 'select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_clickcount desc'
    cursor.execute(sql)
    cc = cursor.fetchall()
    cc_1 = cc[0]
    cc = cc[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '玄幻')
    cursor.execute(sql)
    xuanhuan = cursor.fetchall()
    xuanhuan_1 = xuanhuan[0]
    xuanhuan = xuanhuan[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '二次元')
    cursor.execute(sql)
    erciyuan = cursor.fetchall()
    erciyuan_1 = erciyuan[0]
    erciyuan = erciyuan[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '武侠')
    cursor.execute(sql)
    wuxia = cursor.fetchall()
    wuxia_1 = wuxia[0]
    wuxia = wuxia[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '仙侠')
    cursor.execute(sql)
    xianxia = cursor.fetchall()
    xianxia_1 = xianxia[0]
    xianxia = xianxia[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '都市')
    cursor.execute(sql)
    dushi = cursor.fetchall()
    dushi_1 = dushi[0]
    dushi = dushi[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '现实')
    cursor.execute(sql)
    xianshi = cursor.fetchall()
    xianshi_1 = xianshi[0]
    xianshi = xianshi[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '军事')
    cursor.execute(sql)
    junshi = cursor.fetchall()
    junshi_1 = junshi[0]
    junshi = junshi[1:10]
    sql = "select book_id,book_name,book_clickcount,kind_name,n_user.user_id,user_name,book_image from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
        '历史')
    cursor.execute(sql)
    lishi = cursor.fetchall()
    lishi_1 = lishi[0]
    lishi = lishi[1:10]
    return render(request, 'ranking list.html',
                  {'cc_1': cc_1, 'tc_1': tc_1, 'bw_1': bw_1, 'sc_1': sc_1, 'cc': cc, 'tc': tc, 'bw': bw, 'sc': sc,
                   'xuanhuan_1': xuanhuan_1, 'xuanhuan': xuanhuan, 'erciyuan': erciyuan, 'erciyuan_1': erciyuan_1,
                   'wuxia_1': wuxia_1, 'wuxia': wuxia, 'xianxia': xianxia, 'xianxia_1': xianxia_1, 'dushi_1': dushi_1,
                   'dushi': dushi, 'xianshi_1': xianshi_1, 'xianshi': xianshi, 'junshi_1': junshi_1, 'junshi': junshi,
                   'lishi_1': lishi_1, 'lishi': lishi})


def ranking_click(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        if request.method == 'POST':
            sid = request.POST.get('sid')
            kind = request.POST.get('kind')
            number = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'}
            for i in sid:
                if i not in number:
                    sid = '1'
                    break
            # print(sid)
            sid = int(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_clickcount desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid < 1:
                sid = 1
            elif sid > len(list1):
                sid = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            # print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            return render(request, 'ranking_click.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
        elif request.method == 'GET':
            sid = request.GET.get('sid')
            kind = request.GET.get('kind')
            a = sid.split(',')
            sid = int(a[0])
            b = a[1].split('=')
            snum = int(b[1])
            # print(sid,snum)
            sid = sid + snum
            sid = int(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_clickcount desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_clickcount desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            # print(result)
            return render(request, 'ranking_click.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
    except:
        sid = 1
        list1 = []
        sql = 'select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_clickcount desc'
        cursor.execute(sql)
        result = cursor.fetchall()
        alllen = len(result)
        if len(result) % 5 == 0:
            length = len(result) // 5
        else:
            length = len(result) // 5 + 1
        for x in range(1, length + 1):
            {
                list1.append(x)
            }
        # print(list1)
        a = 5 * (sid - 1)
        b = 5 * sid
        # print(a,b)
        result = result[a:b]
        # print(result)
        z = 1
        m = 1
        n = len(list1)
        list1 = list1[0:3]
        return render(request, 'ranking_click.html',
                      {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'z': z, 'm': m, 'n': n,
                       'kind': kind})


def ranking_ticket(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        if request.method == 'POST':
            sid = request.POST.get('sid')
            kind = request.POST.get('kind')
            print(kind, sid)
            number = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'}
            if sid is None or sid == 'None':
                sid = '1'
            else:
                for i in sid:
                    if i not in number:
                        sid = '1'
                        break
            sid = int(sid)
            print(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_ticketcount desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_ticketcount desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid < 1:
                sid = 1
            elif sid > len(list1):
                sid = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            # print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            return render(request, 'ranking_ticket.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
        elif request.method == 'GET':
            sid = request.GET.get('sid')
            kind = request.GET.get('kind')
            a = sid.split(',')
            sid = int(a[0])
            b = a[1].split('=')
            snum = int(b[1])
            # print(sid,snum)
            sid = sid + snum
            sid = int(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_ticketcount desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_ticketcount desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            # print(result)
            return render(request, 'ranking_ticket.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
    except:
        sid = 1
        list1 = []
        sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_ticketcount desc"
        cursor.execute(sql)
        result = cursor.fetchall()
        alllen = len(result)
        if len(result) % 5 == 0:
            length = len(result) // 5
        else:
            length = len(result) // 5 + 1
        for x in range(1, length + 1):
            {
                list1.append(x)
            }
        # print(list1)
        a = 5 * (sid - 1)
        b = 5 * sid
        # print(a,b)
        result = result[a:b]
        # print(result)
        z = 1
        m = 1
        n = len(list1)
        list1 = list1[0:3]
        return render(request, 'ranking_ticket.html',
                      {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'z': z, 'm': m, 'n': n,
                       'kind': kind})


def ranking_sell(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        if request.method == 'POST':
            sid = request.POST.get('sid')
            kind = request.POST.get('kind')
            number = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'}
            for i in sid:
                if i not in number:
                    sid = '1'
                    break
            sid = int(sid)
            # print(sid)
            list1 = []
            if kind is None or kind == 'None':
                kind == 'None'
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_sellcount desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_sellcount desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid < 1:
                sid = 1
            elif sid > len(list1):
                sid = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            # print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            return render(request, 'ranking_sell.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
        elif request.method == 'GET':
            sid = request.GET.get('sid')
            kind = request.GET.get('kind')
            # print(kind)
            # print(sid, kind)
            a = sid.split(',')
            sid = int(a[0])
            b = a[1].split('=')
            snum = int(b[1])
            # print(sid,snum)
            sid = sid + snum
            sid = int(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_sellcount desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_sellcount desc" % (
                    kind)
            # sql=sql+"AND"+'n_kind.kind_name'+"='"+kind+"'"+"order by book_sellcount desc"
            cursor.execute(sql)
            result = cursor.fetchall()
            # print(result)
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            # print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            # print(result)
            return render(request, 'ranking_sell.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
    except:
        sid = 1
        list1 = []
        sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_sellcount desc"
        cursor.execute(sql)
        result = cursor.fetchall()
        alllen = len(result)
        if len(result) % 5 == 0:
            length = len(result) // 5
        else:
            length = len(result) // 5 + 1
        for x in range(1, length + 1):
            {
                list1.append(x)
            }
        # print(list1)
        a = 5 * (sid - 1)
        b = 5 * sid
        # print(a,b)
        result = result[a:b]
        # print(result)
        z = 1
        m = 1
        n = len(list1)
        list1 = list1[0:3]
        return render(request, 'ranking_sell.html',
                      {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'z': z, 'm': m, 'n': n,
                       'kind': kind})


def ranking_len(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        if request.method == 'POST':
            sid = request.POST.get('sid')
            kind = request.POST.get('kind')
            number = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'}
            for i in sid:
                if i not in number:
                    sid = '1'
                    break
            sid = int(sid)
            # print(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_words desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_words desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid < 1:
                sid = 1
            elif sid > len(list1):
                sid = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            # print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            return render(request, 'ranking_len.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
        elif request.method == 'GET':
            sid = request.GET.get('sid')
            kind = request.GET.get('kind')
            a = sid.split(',')
            sid = int(a[0])
            b = a[1].split('=')
            snum = int(b[1])
            # print(sid,snum)
            sid = sid + snum
            sid = int(sid)
            list1 = []
            if kind is None or kind == 'None':
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_words desc"
            else:
                sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id and n_kind.kind_name='%s' order by book_words desc" % (
                    kind)
            cursor.execute(sql)
            result = cursor.fetchall()
            alllen = len(result)
            if len(result) % 5 == 0:
                length = len(result) // 5
            else:
                length = len(result) // 5 + 1
            # print(length)
            for x in range(1, length + 1):
                {
                    list1.append(x)
                }
            z = 1
            m = 1
            n = len(list1)
            if sid - 2 > 1 and sid + 2 < len(list1):
                z = 3
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 == 1 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 > 1 and sid + 2 == len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            elif sid - 2 <= 0 and sid + 2 < len(list1):
                z = 1
                m = 1
                n = len(list1)
                list1 = list1[0:sid + 2]
            elif sid - 2 > 1 and sid + 2 > len(list1):
                z = 2
                m = 1
                n = len(list1)
                list1 = list1[sid - 3:sid + 2]
            print(list1)
            a = 5 * (sid - 1)
            b = 5 * sid
            # print(a, b)
            result = result[a:b]
            # print(result)
            return render(request, 'ranking_len.html',
                          {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'length': length, 'z': z,
                           'm': m, 'n': n, 'kind': kind})
    except:
        sid = 1
        list1 = []
        sql = "select * from n_bookinfo,n_kind,n_user where n_bookinfo.kind_id=n_kind.kind_id and n_bookinfo.user_id = n_user.user_id order by book_words desc"
        cursor.execute(sql)
        result = cursor.fetchall()
        alllen = len(result)
        if len(result) % 5 == 0:
            length = len(result) // 5
        else:
            length = len(result) // 5 + 1
        for x in range(1, length + 1):
            {
                list1.append(x)
            }
        # print(list1)
        a = 5 * (sid - 1)
        b = 5 * sid
        # print(a,b)
        result = result[a:b]
        # print(result)
        z = 1
        m = 1
        n = len(list1)
        list1 = list1[0:3]
        return render(request, 'ranking_len.html',
                      {'alllen': alllen, 'result': result, 'list1': list1, 'sid': sid, 'z': z, 'm': m, 'n': n,
                       'kind': kind})


def self(request):
    list = []
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        user = request.session.get('user_id')
        if user == None:
            return render(request, 'login.html')
    except:
        return render(request, 'login.html')
    # print(user)
    cursor.execute("select * from n_fans where fans_id='%s'" % (user))
    guanzhushu = cursor.fetchall()
    guanzhushu = len(guanzhushu)
    cursor.execute("select * from n_fans where user_id='%s'" % (user))
    fensishu = cursor.fetchall()
    fensishu = len(fensishu)
    cursor.execute("select * from n_fans a,n_user b where a.user_id=b.user_id and a.fans_id='%s'" % (user))
    myguanzhu = cursor.fetchall()
    myguanzhu = myguanzhu[0:5]
    cursor.execute("select * from n_fans  where user_id='%s'" % (user))
    myfensi = cursor.fetchall()
    myfensi = myfensi[0:5]
    for i in myfensi:
        cursor.execute("select * from n_user where user_id='%s'" % (i['fans_id']))
        result = cursor.fetchone()
        list.append(result)
    # print(myguanzhu,myfensi)
    sql = "select * from n_useraccount,n_user where n_user.user_id=n_useraccount.user_id and n_user.user_id='%s'" % (
        user)
    cursor.execute(sql)
    userinfo = cursor.fetchone()
    # print(userinfo)
    if request.is_ajax():
        book = request.GET.get('book')
        sql = "delete from n_bookcase where book_id='%s'" % (book)
        cursor.execute(sql)
        conn.commit()
    cursor.execute(
        "select * from n_bookcase join n_bookinfo on n_bookcase.book_id=n_bookinfo.book_id where n_bookcase.user_id=%s",
        [user, ])
    book_list = cursor.fetchall()
    a = len(book_list)
    # print(book_list)
    # userid=userinfo[0]['user_id']
    # sql="select * from n_bookcase,n_bookinfo where n_bookinfo.book_id = n_bookcase.book_id and n_bookcase.user_id='%s'"%(userid)
    # cursor.execute(sql)
    # bookinfo=cursor.fetchall()
    return render(request, 'self.html',
                  {'user': userinfo, 'book_list': book_list, 'guanzhushu': guanzhushu, 'fensishu': fensishu,
                   'myguanzhu': myguanzhu, 'myfensi': list, 'a': a})


def guanzhufensi(request):
    list = []
    info = ''
    try:
        user_id = request.session['user_id']
        info = 'in'
    except:
        return redirect('/login/')
    zhuangtai = request.GET.get('zhuangtai')
    if info == 'in':
        cursor.execute("select * from n_fans a,n_user b where a.user_id=b.user_id and a.fans_id=%s", [user_id])
        myguanzhu = cursor.fetchall()
        cursor.execute("select * from n_fans  where user_id='%s'" % (user_id))
        myfensi = cursor.fetchall()
        print(myguanzhu, myfensi)
        for i in myfensi:
            cursor.execute("select * from n_user where user_id='%s'" % (i['fans_id']))
            result = cursor.fetchone()
            list.append(result)
        for i in myguanzhu:
            cursor.execute("select * from n_fans where user_id=%s", [i['user_id']])
            result = cursor.fetchall()
            z = len(result)
            i['guanzhushu'] = z
            cursor.execute("select * from n_fans where fans_id=%s", [i['user_id']])
            result = cursor.fetchall()
            m = len(result)
            i['fensishu'] = m
        for i in list:
            cursor.execute("select * from n_fans where user_id=%s", [i['user_id']])
            result = cursor.fetchall()
            z = len(result)
            i['guanzhushu'] = z
            cursor.execute("select * from n_fans where fans_id=%s", [i['user_id']])
            result = cursor.fetchall()
            m = len(result)
            i['fensishu'] = m
        alen = len(myguanzhu)
        blen = len(myfensi)

    return render(request, 'guanzhufensi.html',
                  {'myguanzhu': myguanzhu, 'myfensi': list, 'alen': alen, 'blen': blen, 'zhuangtai': zhuangtai})


def selfset(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    if request.method == "GET":
        try:
            user = request.session.get('user_id')
            if user == None:
                return render(request, 'login.html')
        except:
            return render(request, 'login.html')
        if request.is_ajax():
            text = request.GET.get('text')
            sql = "update n_user set user_introduction ='%s' where user_id='%s'" % (text, user)
            cursor.execute(sql)
            conn.commit()
            # print(text,user)
        sql = "select * from n_useraccount,n_user where n_user.user_id=n_useraccount.user_id and n_user.user_id='%s'" % (
            user)
        cursor.execute(sql)
        userinfo = cursor.fetchone()
        # print(userinfo['user_introduction'])
        # print(userinfo['user_birth'])
        s = userinfo['user_address'].split('-')
        # print(s)
        address = {'province': s[0], 'city': s[1], 'area': s[2]}
        return render(request, 'selfset.html', {'user': userinfo, 'address': address, 'a': 1})
        # return redirect('/self/')
    else:
        user = request.session.get('user_id')
        if user == None:
            return render(request, 'login.html')
        username = request.POST.get('nickname')
        sex = request.POST.get('sex')
        date = request.POST.get('date')
        city = request.POST.get('city')
        province = request.POST.get('province')
        area = request.POST.get('district')
        sql = "select * from n_useraccount,n_user where n_user.user_id=n_useraccount.user_id and n_user.user_id='%s'" % (
            user)
        cursor.execute(sql)
        userinfo = cursor.fetchone()
        # print(userinfo['user_introduction'])
        # print(userinfo['user_birth'])
        s = userinfo['user_address'].split('-')
        # print(s)
        address = {'province': s[0], 'city': s[1], 'area': s[2]}		
        try:
            # print('________')
            File = request.FILES.get("img")
            # print(File)
            with open('media/upload/img/' + File.name, 'wb')as f:  # with open 无法创建文件夹，需要自己创建
                for chunk in File.chunks():
                    f.write(chunk)
            access_key = 'kWMzCb6yGVJllsSSiYShDXZ3AIRyJ3T0FLAB96hF'
            secret_key = 'EOYODZJ8zPfG5UfRU3ddlA1h9iU9N5lJAqKQaFoi'
            q = Auth(access_key, secret_key)
            bucket_name = 'kuyue'
            key = File.name
            localfile = r"media/upload/img/" + File.name

            try:
                res = imgTest.run(localfile)
                print('<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<>>>>>>>')
                print(res['conclusion'], res['data'][0]['msg'])
                if res['conclusion'] == '不合规':
                    return render(request, 'selfset.html',
                                  {'bag': '图片' + res['conclusion'] + ',' + res['data'][0]['msg'] + ',上传失败！','user': userinfo, 'address': address, 'a': 1})
            except:
                pass

            token = q.upload_token(bucket_name, key)
            ret, info = put_file(token, key, localfile)
            src = 'http://phd9lbnhk.bkt.clouddn.com/' + File.name
            cursor.execute("update n_user set user_image=%s where user_id=%s", [src, user])
            conn.commit()
        except:
            i = 1
        # print(File)
        sql = "select * from n_bookinfo,n_bookcase where n_bookinfo.book_id=n_bookcase.book_id and n_bookcase.user_id='%s'" % (
            user)
        cursor.execute(sql)
        book_list = cursor.fetchall()
        # print(type(city),province,area)
        sql = "update n_user set user_birth='%s' where user_id='%s'" % (date, user)
        cursor.execute(sql)
        conn.commit()
        sql = "update n_user set user_name='%s' where user_id='%s'" % (username, user)
        cursor.execute(sql)
        conn.commit()
        sql = "update n_user set user_sex='%s' where user_id='%s'" % (sex, user)
        cursor.execute(sql)
        conn.commit()
        try:
            province = int(province)
            sql = "select * from n_province where code='%s'" % (province)
            cursor.execute(sql)
            province = cursor.fetchone()
            province = province['name']
            sql = "select * from n_city where code='%s'" % (city)
            cursor.execute(sql)
            city = cursor.fetchone()
            city = city['name']
            sql = "select * from n_area where code='%s'" % (area)
            cursor.execute(sql)
            area = cursor.fetchone()
            area = area['name']
            address = province + '-' + city + '-' + area
            # print(address)
            sql = "update n_user set user_address='%s' where user_id='%s'" % (address, user)
            cursor.execute(sql)
            conn.commit()
            # print(nickname, nameid, sex_dict[sex], date, city, province, district)
        except:
            address = province + '-' + city + '-' + area
            # print(address)
            sql = "update n_user set user_address='%s' where user_id='%s'" % (address, user)
            cursor.execute(sql)
            conn.commit()
            # print(nickname, nameid, sex_dict[sex], date, city, province, district)
        sql = "select * from n_useraccount,n_user where n_user.user_id=n_useraccount.user_id and n_useraccount.user_id='%s'" % (
            user)
        cursor.execute(sql)
        userinfo = cursor.fetchone()
        print(book_list)
        request.session['user_name'] = userinfo['user_name']
        # return render(request, 'self.html', {'user': userinfo, 'book_list': book_list})
        return redirect('/self/')


# File = request.FILES.get("img")
# with open('media/upload/img/' + File.name, 'wb')as f:  # with open 无法创建文件夹，需要自己创建
#     for chunk in File.chunks():
#         f.write(chunk)
# access_key = 'O4uhB24lIhk5B_VR1Tj6IQ1pF0kKA18aIpEzs3Kv'
# secret_key = 'aEEnRGPsAGIRZCobHxBuRmao0Z3wQfi7NyQxzZJV'
# q = Auth(access_key, secret_key)
# bucket_name = 'tupian'
# key = File.name
# localfile = r"media/upload/img/" + File.name
# token = q.upload_token(bucket_name, key)
# ret, info = put_file(token, key, localfile)
# src = 'http://files.g4.xmgc360.com/' + File.name
# cursor.execute("update n_user set user_image=%s where user_id=%s", [src, 1])
# conn.commit()
# return render(request, 'self.html', {'src': src})


def getProvince(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = "select * from n_province "
    cursor.execute(sql)
    provinces = cursor.fetchall()
    res = []
    for i in provinces:
        res.append([i['code'], i['name']])
    return JsonResponse({'provinces': res})


def getCity(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    city_id = request.GET.get('city_id')
    sql = "select * from n_city where provincecode='%s'" % (city_id)
    cursor.execute(sql)
    cities = cursor.fetchall()
    res = []
    for i in cities:
        res.append([i['code'], i['name']])
    return JsonResponse({'cities': res})


def getDistrict(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    district_id = request.GET.get('district_id')
    sql = "select * from n_area where citycode='%s'" % (district_id)
    cursor.execute(sql)
    citys = cursor.fetchall()
    res = []
    for i in citys:
        res.append([i['code'], i['name']])
    return JsonResponse({'district': res})


# 周磊

global fur
fur = True


def classify(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    type = request.GET.get('type')
    state = request.GET.get('state')
    shuxing = request.GET.get('shuxing')
    word = request.GET.get('word')

    sql = 'select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id '
    if type == None and state == None and shuxing == None and word == None:
        sql = 'select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id '
    else:
        if (type != 'None' and type != 'all'):
            sql = sql + " AND " + 'kind_name' + " = '" + dict1[type] + "'"
        if (state != 'None' and state != 'all' and state != ''):
            sql = sql + " AND " + 'a.book_state' + " = '" + dict2[state] + "'"
        if (shuxing != 'None' and shuxing != 'all' and shuxing != ''):
            sql = sql + " AND " + 'a.book_price' + " between '" + str(dict3[shuxing][0]) + "'AND'" + str(
                dict3[shuxing][1]) + "'"
        if (word != 'None' and word != 'all' and word != ''):
            sql = sql + " AND " + "a.book_words" + " between '" + str(dict4[word][0]) + "'AND'" + str(
                dict4[word][1]) + "'"

    print(sql)
    cursor.execute(sql)
    Contacts = cursor.fetchall()
    global fur
    if fur == True:
        random.shuffle(Contacts)
        fur = False
    paginator = Paginator(Contacts, 12)
    try:
        page = int(request.GET.get('page'))
    except:
        page = 1
    try:
        page1 = int(request.GET.get('page'))
    except:
        page1 = None

    if page1 != None:
        page = page1
    try:
        contacts = paginator.page(page)
    except PageNotAnInteger:

        contacts = paginator.page(1)
    except (EmptyPage):
        contacts = paginator.page(paginator.num_pages)
    index = contacts.number - 1
    max_index = len(paginator.page_range)
    start_index = index - 2 if index >= 2 else 0
    end_index = index + 2 if index <= max_index - 2 else max_index
    page_range = paginator.page_range[start_index:end_index]

    return render(request, 'classify.html',
                  {'type': type, 'state': state, 'shuxing': shuxing, 'word': word, 'contacts': contacts,
                   'page_range': page_range, 'start_index': start_index, 'end_index': end_index, 'dict1': dict1,
                   'dict2': dict2, 'dict5': dict5, 'dict6': dict6})


def row(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    type = request.GET.get('type')
    state = request.GET.get('state')
    shuxing = request.GET.get('shuxing')
    word = request.GET.get('word')

    print(type)
    print(state)
    print(shuxing)
    print(word)

    sql = 'select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id '
    if type == None and state == None and shuxing == None and word == None:
        sql = 'select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id '
    else:
        if (type != 'None' and type != 'all'):
            sql = sql + " AND " + 'b.kind_name' + " = '" + dict1[type] + "'"
        if (state != 'None' and state != 'all'):
            sql = sql + " AND " + 'a.book_state' + " = '" + dict2[state] + "'"
        if (shuxing != 'None' and shuxing != 'all'):
            sql = sql + " AND " + 'a.book_price' + " between '" + str(dict3[shuxing][0]) + "'AND'" + str(
                dict3[shuxing][1]) + "'"
        if (word != 'None' and word != 'all'):
            sql = sql + " AND " + "a.book_words" + " between '" + str(dict4[word][0]) + "'AND'" + str(
                dict4[word][1]) + "'"

    print(sql)
    cursor.execute(sql)
    Contacts = cursor.fetchall()
    global fur
    if fur == True:
        random.shuffle(Contacts)
        fur = False
    paginator = Paginator(Contacts, 18)
    try:
        page = int(request.GET.get('page'))
    except:
        page = 1
    try:
        page1 = int(request.GET.get('page'))
    except:
        page1 = None

    if page1 != None:
        page = page1
    try:
        contacts = paginator.page(page)
    except PageNotAnInteger:

        contacts = paginator.page(1)
    except (EmptyPage):
        contacts = paginator.page(paginator.num_pages)
    index = contacts.number - 1
    max_index = len(paginator.page_range)
    start_index = index - 2 if index >= 2 else 0
    end_index = index + 2 if index <= max_index - 2 else max_index
    page_range = paginator.page_range[start_index:end_index]

    return render(request, 'classify-row.html',
                  {'type': type, 'state': state, 'shuxing': shuxing, 'word': word, 'contacts': contacts,
                   'page_range': page_range, 'start_index': start_index, 'end_index': end_index, 'dict1': dict1,
                   'dict2': dict2, 'dict5': dict5, 'dict6': dict6})


def wanben(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    type = request.GET.get('type')
    state = request.GET.get('state')
    shuxing = request.GET.get('shuxing')
    word = request.GET.get('word')

    print(type)
    print(state)
    print(shuxing)
    print(word)

    sql = "select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id and a.book_state='完本'"
    if type == None and state == None and shuxing == None and word == None:
        sql = "select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id and a.book_state='完本'"
    else:
        if (type != 'None' and type != 'all'):
            sql = sql + " AND " + 'b.kind_name' + " = '" + dict1[type] + "'"
        # if (state != 'None' and state != 'all'):
        #     sql = sql + " AND " + 'a.book_state' + " = '" + dict2[state] + "'"
        if state == 'lianzai':
            sql = sql.replace("and a.book_state='完本'", "and a.book_state='连载中'")
        if (shuxing != 'None' and shuxing != 'all'):
            sql = sql + " AND " + 'a.book_price' + " between '" + str(dict3[shuxing][0]) + "'AND'" + str(
                dict3[shuxing][1]) + "'"

        if (word != 'None' and word != 'all'):
            sql = sql + " AND " + "a.book_words" + " between '" + str(dict4[word][0]) + "'AND'" + str(
                dict4[word][1]) + "'"

    print(sql)
    cursor.execute(sql)
    Contacts = cursor.fetchall()
    global fur
    if fur == True:
        random.shuffle(Contacts)
        fur = False
    paginator = Paginator(Contacts, 12)
    try:
        page = int(request.GET.get('page'))
    except:
        page = 1
    try:
        page1 = int(request.GET.get('page'))
    except:
        page1 = None

    if page1 != None:
        page = page1
    try:
        contacts = paginator.page(page)
    except PageNotAnInteger:

        contacts = paginator.page(1)
    except (EmptyPage):
        contacts = paginator.page(paginator.num_pages)
    index = contacts.number - 1
    max_index = len(paginator.page_range)
    start_index = index - 2 if index >= 2 else 0
    end_index = index + 2 if index <= max_index - 2 else max_index
    page_range = paginator.page_range[start_index:end_index]

    return render(request, 'wanben.html',
                  {'type': type, 'state': state, 'shuxing': shuxing, 'word': word, 'contacts': contacts,
                   'page_range': page_range, 'start_index': start_index, 'end_index': end_index, 'dict1': dict1,
                   'dict2': dict2, 'dict5': dict5, 'dict6': dict6})


def free(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    Contacts = []
    cursor.execute(
        "select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id and book_price>0")
    Contact = cursor.fetchall()
    Contact = Contact[:9]
    for row in Contact:
        cursor.execute("select chapter_id from n_chapter where book_id=%s", [row['book_id']])
        result = cursor.fetchall()
        if result != ():
            row['chapter_id'] = result[0]['chapter_id']
            Contacts.append(row)

    print(Contacts)
    try:
        user_id = request.session['user_id']
        for i in Contacts:
            book_id = i['book_id']
            cursor.execute("select * from n_bookcase where user_id=%s and book_id=%s", [user_id, book_id, ])
            bookcase = cursor.fetchone()
            if bookcase == None:
                i['shujia'] = '加入书架'
            else:
                i['shujia'] = '已收藏'
    except:
        for i in Contacts:
            i['shujia'] = '加入书架'
    return render(request, 'time-free.html', {'Contacts': Contacts})


def freeAll(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    type = request.GET.get('type')
    state = request.GET.get('state')
    shuxing = request.GET.get('shuxing')
    word = request.GET.get('word')

    print(type)
    print(state)
    print(shuxing)
    print(word)

    sql = 'select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id and a.book_price=0'
    if type == None and state == None and shuxing == None and word == None:
        sql = 'select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id and a.book_price=0 '
    else:
        if (type != 'None' and type != 'all'):
            sql = sql + " AND " + 'b.kind_name' + " = '" + dict1[type] + "'"
        if (state != 'None' and state != 'all'):
            sql = sql + " AND " + 'a.book_state' + " = '" + dict2[state] + "'"

        if shuxing == 'fufei':
            sql = sql.replace('and a.book_price=0', 'and a.book_price>0')
        if (word != 'None' and word != 'all'):
            sql = sql + " AND " + "a.book_words" + " between '" + str(dict4[word][0]) + "'AND'" + str(
                dict4[word][1]) + "'"

    print(sql)
    cursor.execute(sql)
    Contacts = cursor.fetchall()
    global fur
    if fur == True:
        random.shuffle(Contacts)
        fur = False
    paginator = Paginator(Contacts, 12)
    try:
        page = int(request.GET.get('page'))
    except:
        page = 1
    try:
        page1 = int(request.GET.get('page'))
    except:
        page1 = None

    if page1 != None:
        page = page1
    try:
        contacts = paginator.page(page)
    except PageNotAnInteger:

        contacts = paginator.page(1)
    except (EmptyPage):
        contacts = paginator.page(paginator.num_pages)
    index = contacts.number - 1
    max_index = len(paginator.page_range)
    start_index = index - 2 if index >= 2 else 0
    end_index = index + 2 if index <= max_index - 2 else max_index
    page_range = paginator.page_range[start_index:end_index]

    return render(request, 'freeAll.html',
                  {'type': type, 'state': state, 'shuxing': shuxing, 'word': word, 'contacts': contacts,
                   'page_range': page_range, 'start_index': start_index, 'end_index': end_index, 'dict1': dict1,
                   'dict2': dict2, 'dict5': dict5, 'dict6': dict6})


def shoplog(request):
    return render(request, 'shoplog.html')


def shop(request):
    return render(request, 'shopping.html')


def dashang(request):
    return render(request, 'dashang.html')


def pay(request):
    return render(request, 'pay.html')


# 分词函数
def myjie():
    list = []
    print('=====================')
    cursor.execute(
        "select book_name,user_name,book_id,kind_name from n_bookinfo a,n_user b,n_kind c where a.user_id=b.user_id and a.kind_id=c.kind_id")
    result = cursor.fetchall()
    for row in result:

        id = row['book_id']
        ks = row['kind_name']
        r.sadd(ks, id)
        for item in row['book_name']:
            key = item
            r.sadd(key, id)
        for item in row['user_name']:
            key = item
            r.sadd(key, id)
        for item in row['kind_name']:
            key = item
            r.sadd(key, id)

        word_list = jieba.cut_for_search(row['book_name'])
        for data in word_list:
            key = data
            r.sadd(key, id)
        word_list = jieba.cut_for_search(row['user_name'])
        for data in word_list:
            key = data
            r.sadd(key, id)


# 搜索
def query(request):
    lst = []
    name = request.GET.get('keys')
    # name = request.POST.get('keyword')
    print(name)
    key = name
    s = r.smembers(key)
    print(s)
    for i in s:
        cursor.execute(
            "select * from n_bookinfo a,n_kind b,n_user c where a.kind_id=b.kind_id and a.user_id=c.user_id and book_id=%s",
            [i])

        result = cursor.fetchone()
        print(result)
        lst.append(result)

    print('====================')

    paginator = Paginator(lst, 5)
    try:
        page = int(request.GET.get('page'))
    except:
        page = 1

    try:
        contacts = paginator.page(page)
    except PageNotAnInteger:

        contacts = paginator.page(1)
    except (EmptyPage):
        contacts = paginator.page(paginator.num_pages)
    index = contacts.number - 1
    max_index = len(paginator.page_range)
    start_index = index - 2 if index >= 2 else 0
    end_index = index + 2 if index <= max_index - 2 else max_index
    page_range = paginator.page_range[start_index:end_index]
    print(start_index, end_index)
    return render(request, 'search.html', {'contacts': contacts, 'name': name})


def get_ali_object(id):
    # 沙箱环境地址：https://openhome.alipay.com/platform/appDaily.htm?tab=info
    app_id = "2016092000552547"  # APPID （沙箱应用）

    # 支付完成后，支付偷偷向这里地址发送一个post请求，识别公网IP,如果是 192.168.20.13局域网IP ,支付宝找不到，def page2() 接收不到这个请求
    # notify_url = "http://47.94.172.250:8804/page2/"
    notify_url = "http://127.0.0.1:8000/page2/"

    # 支付完成后，跳转的地址。
    id = id.replace('buy', 'Details')
    return_url = "http://127.0.0.1:8000" + id
    print('哈哈')

    print(id)
    merchant_private_key_path = "keys/应用私钥2048.txt"  # 应用私钥
    alipay_public_key_path = "keys/应用公钥2048.txt"  # 支付宝公钥

    alipay = AliPay(
        appid=app_id,
        app_notify_url=notify_url,
        return_url=return_url,
        app_private_key_path=merchant_private_key_path,
        alipay_public_key_path=alipay_public_key_path,  # 支付宝的公钥，验证支付宝回传消息使用，不是你自己的公钥
        debug=True,  # 默认False,
    )

    return alipay


def buy(request):
    try:
        user_id = request.session['user_id']
    except:
        return redirect('/login/')
    nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    book_id = request.GET.get('book_id')

    money = float(request.GET.get('book_price'))
    id = request.get_full_path()

    alipay = get_ali_object(id)
    print('kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk')
    print(alipay)
    # 生成支付的url
    query_params = alipay.direct_pay(
        subject="酷阅小说精品",  # 商品简单描述
        out_trade_no="x2" + str(time.time()),  # 用户购买的商品订单号（每次不一样） 20180301073422891
        total_amount=money,  # 交易金额(单位: 元 保留俩位小数)
        # book_id=book_id,
    )
    cursor.execute(
        "select * from n_bookinfo a,n_user b,n_kind c where a.user_id=b.user_id and a.kind_id=c.kind_id and book_id=%s",
        [book_id])
    result = cursor.fetchone()
    cursor.execute("insert into n_buyrecord (book_id,buy_time,user_id)values(%s,%s,%s)",
                   [result['book_id'], nowTime, user_id])
    conn.commit()
    pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）

    return redirect(pay_url)


def page2(request):
    alipay = get_ali_object(id)

    if request.method == "POST":

        from urllib.parse import parse_qs
        # name&age=123....
        body_str = request.body.decode('utf-8')
        post_data = parse_qs(body_str)

        post_dict = {}
        for k, v in post_data.items():
            post_dict[k] = v[0]

        # post_dict有10key： 9 ，1
        sign = post_dict.pop('sign', None)
        status = alipay.verify(post_dict, sign)
        print('------------------开始------------------')
        print('POST验证', status)
        print(post_dict)
        out_trade_no = post_dict['out_trade_no']

        # 修改订单状态
        # models.Order.objects.filter(trade_no=out_trade_no).update(status=2)
        print('------------------结束------------------')
        # 修改订单状态：获取订单号

        return HttpResponse('POST返回')


    else:

        params = request.GET.dict()
        sign = params.pop('sign', None)
        status = alipay.verify(params, sign)
        print('==================开始==================')
        print('GET验证', status)
        print('==================结束==================')
        return HttpResponse('支付成功')


def record(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    alist = []
    info = ''
    try:
        user_id = request.session['user_id']
        info = 'in'
    except:
        return redirect('/login/')
    if info == 'in':
        cursor.execute("select * from n_buyrecord a,n_bookinfo b where a.book_id=b.book_id and a.user_id=%s", [user_id])
        result = cursor.fetchall()

        cursor.execute(
            "select max(read_id)  from n_readrecord a,n_bookinfo b  where a.book_id=b.book_id and a.user_id=%s group by a.book_id",
            [user_id])
        result1 = cursor.fetchall()

        for row in result1:
            cursor.execute(
                "select * from n_readrecord a,n_bookinfo b,n_chapter c where a.book_id=b.book_id  and a.chapter_id=c.chapter_id and read_id=%s",
                [row['max(read_id)']])
            dt = cursor.fetchone()
            alist.append(dt)
        # 分页1

        try:
            page = int(request.GET.get('page'))
        except:
            page = 1
        paginator = Paginator(result, 5)

        try:
            contacts = paginator.page(page)
        except PageNotAnInteger:

            contacts = paginator.page(1)
        except (EmptyPage):
            contacts = paginator.page(paginator.num_pages)
        index = contacts.number - 1
        max_index = len(paginator.page_range)
        start_index = index - 3 if index >= 3 else 0
        end_index = index + 3 if index <= max_index - 3 else max_index
        page_range = paginator.page_range[start_index:end_index]

        alen = len(alist)
        blen = len(result)
    return render(request, 'shoplog.html',
                  {'result': result, 'alist': alist, 'alen': alen, 'blen': blen, 'contacts': contacts,
                   'start_index': start_index, 'end_index': end_index, 'page_range': page_range})


# def del_buy(request):
#     id = request.GET.get('id')
#     cursor.execute("delete  from n_buyrecord where buy_id=%s", [id])
#     conn.commit()
#     return redirect('/shoplog/')


def del_read(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    id = request.GET.get('id')
    cursor.execute("delete  from n_readrecord where book_id=%s ", [id])
    conn.commit()
    print('MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM')
    print(id)
    return HttpResponse('OK')


# 周永辉
def author(request):
    conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # request.session['login_from']=request.META.get('HTTP_REFERER','/')
    try:
        id = request.session['user_id']
        print('OK')
        print(id)
        cursor.execute(
            "select * from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                id,))
        authorlist = cursor.fetchall()
        cursor.execute(
            "select sum(book_words) from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s' " % (
                id,))
        result=cursor.fetchone()
        words=result['sum(book_words)']
        if words==None:
            words=0
        # print(authorlist)
        cursor.execute("select count(*) from n_fans  where user_id='%s'" % (id,))
        fan = cursor.fetchone()
        fan=fan['count(*)']
        # print(len(fan))
        cursor.execute("select count(*) from n_follow  where user_id='%s'" % (id,))
        follow = cursor.fetchone()
        # print(len(follow))
        follow=follow['count(*)']
        num = len(authorlist)
        if authorlist != ():
            author = authorlist[0]
        else:
            cursor.execute("select * from n_user where user_id=%s",[id])
            author=cursor.fetchone()
        lan=len(authorlist)
        print('++++++++++++++++++++++++++')
        print(author)

        from myapp.pager import Pagination
        current_page = request.GET.get('p')
        page_obj = Pagination(num, current_page)
        data_list = authorlist[page_obj.start():page_obj.end()]
        # return render(request, 'author.html', {'authorlist': data_list, 'page_obj': page_obj, 'num': num})
        return render(request, 'author.html',
                      {'authorlist': data_list, 'author': author, 'page_obj': page_obj, 'num': num, 'fan': fan, 'follow': follow,'words':words,'lan':lan})

    # 直接输出错误信息
    except Exception as error:
        print(error)
        return redirect('/login/')

def guanzhu(request):
    try:
        user_id = request.session['user_id']
        # print(user_id)
        data = request.POST
        if data['caozuo'] == 'guanzhu':
            cursor.execute("insert into  n_fans(user_id,fans_id) values('%s','%s')" % (data['userid'], user_id))
            zhuangtai = "关注成功"
            cursor.execute("select * from n_fans where fans_id='%s'" % (data['userid']))
            guanzhushu = cursor.fetchall()
            guanzhushu = len(guanzhushu)
            cursor.execute("select * from n_fans where user_id='%s'" % (data['userid']))
            fensishu = cursor.fetchall()
            fensishu = len(fensishu)
            # print(guanzhushu)
        elif data['caozuo'] == 'quguan':
            cursor.execute("delete from n_fans where user_id='%s'and fans_id='%s'" % (data['userid'], user_id))
            zhuangtai = "取关成功"
            cursor.execute("select * from n_fans where fans_id='%s'" % (data['userid']))
            guanzhushu = cursor.fetchall()
            guanzhushu = len(guanzhushu)
            cursor.execute("select * from n_fans where user_id='%s'" % (data['userid']))
            fensishu = cursor.fetchall()
            fensishu = len(fensishu)
        conn.commit()
        response = JsonResponse({"zhuangtai": zhuangtai, "guanzhushu": guanzhushu, "fensishu": fensishu})
        return response
    except:
        title = "请先登录！！！"
        response = JsonResponse({"title": title})
        return response


def author_look(request):
    id = request.GET.get('user_id')
    try:
        user_id = request.session['user_id']
        print(user_id, id)
        if int(user_id) == int(id):
            return redirect('/author/')
        else:
            cursor.execute("select * from n_fans where user_id='%s'and fans_id=%s" % (id, user_id))
            guanzhu = cursor.fetchone()
            # print(len(guanzhu))
            if len(guanzhu) == 0:
                guanzhu = 'False'
            else:
                guanzhu = 'True'
            # print(guanzhu)
    except:
        guanzhu = 'False'
    # print(guanzhu)

    cursor.execute("select * from n_fans where fans_id='%s'" % (id))
    guanzhushu = cursor.fetchall()
    guanzhushu = len(guanzhushu)
    cursor.execute("select * from n_fans where user_id='%s'" % (id))
    fensishu = cursor.fetchall()
    fensishu = len(fensishu)
    cursor.execute(
        "select * from n_bookcase a,n_bookinfo b,n_user c,n_kind d where a.book_id=b.book_id and a.user_id=c.user_id and b.kind_id=d.kind_id and a.user_id='%s'" % (
            id,))
    caselist = cursor.fetchall()
    # print(caselist)
    cursor.execute(
        "select * from n_user a,n_bookinfo b,n_kind c where a.user_id=b.user_id and b.kind_id=c.kind_id and a.user_id='%s'" % (
            id,))
    authorlist = cursor.fetchall()
    # print(authorlist)
    num = len(authorlist)
    author = authorlist[0]
    # print(author)
    if len(authorlist) >= 4:
        author_long = authorlist[0:4]
        # print(author_long)
        return render(request, 'author_look.html',
                      {'author_long': author_long, 'num': num, 'author': author, 'caselist': caselist,
                       'guanzhu': guanzhu,
                       'guanzhushu': guanzhushu, 'fensishu': fensishu, })
    else:
        return render(request, 'author_look.html',
                      {'authorlist': authorlist, 'author': author, 'num': num, 'caselist': caselist, 'guanzhu': guanzhu,
                       'guanzhushu': guanzhushu, 'fensishu': fensishu, })


from django.http import JsonResponse


def login(request):
    if request.method == 'GET':
        parent = request.GET.get('href')
        request.session['href'] = parent
        print(parent)
        return render(request, 'login.html')
    elif request.method == 'POST':
        user = request.POST.get('username')
        pwd = request.POST.get('password')
        print(user,pwd)
        conn = pymysql.connect("139.196.136.63", "user1", "123456", "kuyue", charset='utf8')
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        sql = "select * from n_useraccount a,n_user b where a.user_id=b.user_id and  user_account='%s'" % (user,)
        cursor.execute(sql)
        messagelist = cursor.fetchone()
        print(messagelist)
        flag='ok'
        if messagelist ==None:
            flag='usererror'
            respone=JsonResponse({'session':request.session['href'],'flag':flag})
            return respone
        elif pwd=='' and user==messagelist['user_account']:
            flag='nopass'
            respone=JsonResponse({'session':request.session['href'],'flag':flag})
            return respone
        elif messagelist['user_account'] ==user and messagelist['user_password'] != pwd:
            flag='passerror'
            respone=JsonResponse({'session':request.session['href'],'flag':flag})
            return respone

        elif pwd == messagelist['user_password'] and messagelist['user_account'] ==user:
            id = messagelist['user_id']
            name = messagelist['user_name']
            request.session['user'] = user
            request.session['user_id'] = id
            request.session['user_name'] = name
            print(user, id, name)
            request.session.set_expiry(100000)
            # if request.POST.get('submit') == "登录":  # 点击按钮
            #     request.session.set_expiry(1000)  # session认证时间为1000s，1000s之后session认证失效
            # request.session['username'] = user  # user的值发送给session里的username
            request.session['is_login'] = True  # 认证为真
            # if request.session['href']:
            #     return HttpResponseRedirect(request.session['href'])
            # else:
            respone=JsonResponse({'session':request.session['href'],'flag':flag})
            return respone
        else:
            return redirect('/login/')


def loginout(request):
    try:
        del request.session['user']  # 清除session里的user
        del request.session['user_id']
        del request.session['user_name']
    except KeyError:
        pass
    return redirect('/main/')


def register(request):
    return render(request, 'register.html')


def judge(request):
    userphone = request.POST.get('userphone')
    password = request.POST.get('password')
    passwords = request.POST.get('passwords')
    message = request.POST.get('message')
    print(userphone, password, passwords, message)

    cursor.execute("select user_account from n_useraccount where user_account='%s'" % (userphone,))
    user = cursor.fetchall()
    print(user)
    flag = 'ok'

    if userphone == '':
        flag = 'kong'
        return HttpResponse(flag)

    if message == '':
        flag = 'nomsg'
        return HttpResponse(flag)
    if userphone:
        redismsg = r.get(userphone).decode('utf-8')
        print(redismsg)
        if message != redismsg:
            flag = 'msgerror'
            return HttpResponse(flag)

    if password == '' or passwords == '':
        flag = 'nomi'
        return HttpResponse(flag)
    else:
        print(userphone)
        cursor.execute("select max(user_id) from n_useraccount")
        id = cursor.fetchone()
        maxid = id['max(user_id)']
        print(maxid)

        if user:
            flag = 'cunzai'
            return HttpResponse(flag)
        else:
            print(userphone)
            cursor.execute("insert into n_useraccount(user_account,user_password,user_id) values (%s,%s,%s)" % (
                userphone, password, maxid + 1))
            conn.commit()

            import random
            xing = [
                '赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩', '杨', '朱', '秦', '尤', '许',
                '何', '吕', '施', '张', '孔', '曹', '严', '华', '金', '魏', '陶', '姜', '戚', '谢', '邹', '喻', '柏', '水', '窦', '章',
                '云', '苏', '潘', '葛', '奚', '范', '彭', '郎', '鲁', '韦', '昌', '马', '苗', '凤', '花', '方', '俞', '任', '袁', '柳',
                '酆', '鲍', '史', '唐', '费', '廉', '岑', '薛', '雷', '贺', '倪', '汤', '滕', '殷', '罗', '毕', '郝', '邬', '安', '常',
                '乐', '于', '时', '傅', '皮', '卞', '齐', '康', '伍', '余', '元', '卜', '顾', '孟', '平', '黄', '和', '穆', '萧', '尹',
                '姚', '邵', '堪', '汪', '祁', '毛', '禹', '狄', '米', '贝', '明', '臧', '计', '伏', '成', '戴', '谈', '宋', '茅', '庞',
                '熊', '纪', '舒', '屈', '项', '祝', '董', '梁']

            ming = [
                '的', '一', '是', '了', '我', '不', '人', '在', '他', '有', '这', '个', '上', '们', '来', '到', '时', '大', '地', '为',
                '子', '中', '你', '说', '生', '国', '年', '着', '就', '那', '和', '要', '她', '出', '也', '得', '里', '后', '自', '以',
                '会', '家', '平', '报', '友', '关', '放', '至', '张', '认', '接', '告', '入', '笑', '内', '英', '军', '乾', '坤', '美', '飘',
                '民', '岁', '往', '何', '度', '山', '觉', '路', '带', '万', '男', '边', '风', '解', '叫', '任', '金', '快', '原',
                '吃', '妈', '变', '通', '师', '立', '象', '数', '四', '失', '满', '战', '远', '格', '士', '音', '轻', '目', '条', '呢',
                '病', '始', '达', '深', '完', '今', '提', '求', '清', '王', '化', '空', '业', '思', '切', '怎', '非', '找', '片', '罗',
                '钱', '紶', '吗', '语', '元', '喜', '曾', '离', '飞', '科', '言', '干', '流', '欢', '约', '各', '即', '指', '合', '反',
                '题', '必', '该', '论', '剧', '啊', '船', '挥', '鲜', '财', '孤', '枪', '禁', '恐', '伙', '杰', '迹', '妹', '藸', '遍',
                '副', '坦', '牌', '江', '顺', '秋', '萨', '菜', '划', '授', '归', '浪', '听', '凡', '预', '盖', '释', '介', '烧', '误',
                '奶', '雄', '升', '碃', '编', '典', '袋', '莱', '含', '盛', '济', '蒙', '棋', '端', '腿', '招', '释', '介', '烧', '误', ]
            a, b = [], []
            for i in range(1):
                x = random.randint(0, len(xing))
                m1 = random.randint(0, len(ming))
                m2 = random.randint(0, len(ming))
                name = '' + xing[x] + ming[m1] + ming[m2]
                a.append(name)
                print(a)
            b = a
            print(b[0])
            uname = b[0]

            cursor.execute("insert into n_user(user_id,user_name) values ('%s','%s')" % (maxid + 1, uname))
            conn.commit()

            request.session['user_id'] = maxid + 1
            request.session['user_name'] = uname
            request.session.set_expiry(100000)

            return HttpResponse(flag)
    # return HttpResponse(flag)


def send_message(request):
    from urllib import request as rq
    phone = request.POST.get('phone')
    identify = random.randint(100000, 999999)
    print(phone)
    textmod = {
        "sid": "d2ef97ce1db4e67d16389cbba451e413",  # 云之讯Account Sid
        "token": "68cc68b10b469089946c653c9b9cfa72",  # 云之讯Auth Token
        "appid": "2d99c17c9dab4e4387accff6be277aca",  # 云之讯AppId
        "templateid": "396134",  # 短信模板
        "param": identify,
        "mobile": phone,
        "uid": phone
    }
    textmod = json.dumps(textmod).encode(encoding='utf-8')
    header_dict = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko',
                   "Content-Type": "application/json"}
    req = rq.Request(url='https://open.ucpaas.com/ol/sms/sendsms', data=textmod, headers=header_dict)
    res = rq.urlopen(req)
    res = res.read().decode('utf-8')
    print(res)
    r.setex(phone, identify, 60)  # 设置保存在redis的时间
    print(r.get(phone).decode('utf-8'))

    return HttpResponse(json.dumps(res))


# 测试
def test1(request):
    if request.method == "GET":
        return render(request, 'test1.html')
    # # else:
    # # content = request.POST('content')
    # # print(content)
    # # print(type(content))
    # # return render(request, 'main.html')
    # else:
    #     print(123123213)
    # else:
    #     #         print(4511231)
    #     html = request.POST.get("html")
    #     print(html)
    #     ret = 1234
    #     return HttpResponse(json.dumps(ret))
    #         print(data)
    #         print(host)
    #         return HttpResponse(json.dumps(host))
    #         # return render(request, 'main.html')
    if request.is_ajax():
        print(213123123)
        data = request.POST
        host = request.POST.get("host")
        print(data['html'])
        print(host)
        response = JsonResponse({"host": data['html']})
        return response
